SQL Server timestamp timestamp

SQL Server timestamp timestamp

In SQL Server Books Online, it says:

The SQL Server timestamp data type is independent of time and date. SQL Server Timestamp is a binary number that indicates the relative order in which data modifications occur in the database. The implementation of the timestamp data type was originally intended to support the SQL Server recovery algorithm. Each time the page is modified, it is marked with the current @ @DBTS value and then @ @DBTS plus 1. This is sufficient to help the recovery process determine the relative order of page modifications, but the timestamp value has no relation to time.

I'll explain it in words with a spit. Easy to understand:

We know that the number type has an "identity" attribute, set "Identity Seed", "identity increment", each time a record is added, the value of this field will automatically increase on the basis of a recent identification value, so we can know which records are added first, which records are added later, But we have no way of knowing which records have been modified. Timestamp this type of field, each time a record is added, it automatically increases on the basis of a recent timestamp, and when a record is modified, it automatically increases on the basis of a recent timestamp, so we know which records have been modified.

Timestamp This data type shows the automatically generated binary number, ensuring that these numbers are unique in the database. Timestamp is generally used as a mechanism for adding a version stamp to a table row. The storage size is 8 bytes.

Note the Transact-SQL timestamp data type differs from the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

Microsoft®sql Server™ Future Versions may modify the behavior of the Transact-SQL timestamp data type so that it is consistent with the behavior defined in the standard. By then, the current timestamp data type will be replaced with the rowversion data type.

Microsoft®sql server™2000 introduces a rowversion synonym for the timestamp data type. Use rowversion as much as possible in DDL statements instead of using timestamp. RowVersion is subject to the behavior of data type synonyms. For more information, see Data type synonyms.

In the CREATE table or ALTER table statement, you do not have to provide a column name for the timestamp data type:

Create TABLE exampletable (prikey int PRIMARY KEY, timestamp) if the column name is not provided, SQL Server generates a timestamp column name. The rowversion data type synonym does not have this behavior. You must provide a column name when specifying rowversion.

A table can have only one timestamp column. The values in the timestamp column are updated each time the row that contains the timestamp column is inserted or updated. This property makes the timestamp column unsuitable for use as a key, especially if it is not used as a primary key. Any update to the row changes the timestamp value, thereby changing the key value. If the column belongs to a primary key, the old key value will be invalid, and the foreign key referencing the old value will no longer be valid. If the table is referenced in a dynamic cursor, all updates change the position of the row in the cursor. If the column belongs to an index key, all updates to the data row will also cause the index to update.

A non-nullable timestamp column is semantically equivalent to a binary (8) column. The nullable timestamp column is semantically equivalent to the varbinary (8) column.

Microsoft SQL Server Stores the value of a datetime data type internally with two 4-byte integers. The first 4 bytes stores the number of days before or after base date (that is, January 1, 1900). The base date is the system reference date. DateTime values older than January 1, 1753 are not allowed. Another 4 bytes stores the time of day represented by the number of milliseconds after midnight.

---------------------------------------------------------------------------------------------------

@ @DBTS (SELECT @ @DBTS) returns the value of the current timestamp data type for the current database. This timestamp value is guaranteed to be unique in the database.

Syntax @ @DBTS

return type varbinary

Note @ @DBTS returns the timestamp value used at the end of the current database. When a row with the timestamp column is inserted or updated, a new timestamp value is generated.

1

SQL Code

CREATE TABLE XX (id int, timestamp)

INSERT into XX (ID) Select 1

SELECT * FROM XX

2

SQL Code

CREATE TABLE tb_test (id int, ts timestamp)

Insert Tb_test (ID) Select 1

UNION ALL SELECT 2

UNION ALL Select 3

UNION ALL Select 4

SELECT *, Nts=cast (ts as bigint) from Tb_test

drop table Tb_test

3

SQL Code

DECLARE @n bigint

Set @n=8001

Select CAST (@N as timestamp)

Exposes the data type of a unique binary number that is automatically generated in the database. Timestamp are typically used as a mechanism for adding version stamps to table rows. The storage size is 8 bytes. The timestamp data type is just an incremented number and does not hold the date or time. To record a date or time, use the datetime data type.

Note Each database has a counter that is incremented when an INSERT or update operation is performed on a table that contains timestamp columns in the database. The counter is a database timestamp. This can track the relative time within the database, not the actual time associated with the clock. A table can have only one timestamp column. The incremental database timestamp value is inserted in the timestamp column each time the row that contains the timestamp column is modified or inserted. This property makes the timestamp column unsuitable for use as a key, especially if it is not used as a primary key. Any update to the row changes the timestamp value, thereby changing the key value. If the column belongs to a primary key, the old key value will be invalid, and the foreign key referencing the old value will no longer be valid. If the table is referenced in a dynamic cursor, all updates change the position of the row in the cursor. If the column belongs to an index key, all updates to the data row will also cause the index to update.

Using the timestamp column in a row makes it easy to determine whether any value in the row has changed since the last time it was read. If a change is made to the row, the timestamp value is updated. If no changes are made to the row, the timestamp value is consistent with the timestamp value of the row that was previously read. To return the current timestamp value for the database, use @ @DBTS.

The Transact-SQL timestamp data type differs from the timestamp data type that is defined in the SQL-2003 standard. The SQL-2003 timestamp data type is equivalent to the Transact-SQL datetime data type.

The rowversion data type is a synonym for the timestamp data type and has the behavior of a synonym for the data type. In DDL statements, try to use rowversion instead of timestamp. For more information, see Data type synonyms (Transact-SQL).

In the CREATE table or ALTER table statement, you do not have to specify a column name for the timestamp data type, for example:

Copy Code CREATE TABLE exampletable (prikey int PRIMARY KEY, timestamp);

If you do not specify a column name, the Microsoft SQL Server 2005 Database engine generates a timestamp column name, but the rowversion synonym does not behave like this. When you use rowversion, you must specify a column name.

Note: A duplicate timestamp value may be generated when a SELECT INTO statement with the timestamp column in the select list is used. It is not recommended to use timestamp in this way.

MySQL's timestamp is completely different from SQL Server, which has nothing to do with time, just to prevent concurrency, while the former can record time

MySQL TIMESTAMP (timestamp) detailed  timestamp variant 1,timestamp DEFAULT current_timestamp on UPDATE current_timestamp   Refreshes the data column when creating new records and modifying existing records  2,timestamp default Current_timestamp   Set this field to the current time when creating a new record, but no longer refreshes it when you modify it later  3,timestamp on UPDATE current_timestamp   when creating a new record, set this field to 0 and refresh it later when modified  www.2cto.com    4,timestamp default ' Yyyy-mm-dd hh:mm:ss ' on UPDATE current_timestamp   Set this field to the given value when creating a new record, Refresh it at a later time. MySQL does not currently support column default as a function, such as the ability to reach your column defaults for the current update date and time, you can use the timestamp column type to specify the timestamp column type in detail below  * The timestamp column type *timestamp value can start at some point of 1970 until 2037, with a precision of one second and its value displayed as a number. The format of the timestamp value display size is shown in the following table:: +---------------+----------------+| Column Type | Display Format | | TIMESTAMP (14) | YYYYMMDDHHMMSS | | TIMESTAMP (12) | Yymmddhhmmss | | TIMESTAMP (10) | YYMMDDHHMM | | TIMESTAMP (8) | YYYYMMDD | | TIMESTAMP (6) | YYMMDD | | TIMESTAMP (4) | Yymm | | TIMESTAMP (2) | YY |+---------------+----------------+ "full" timestamp format is 14-bit, but timestamp column can also be created with shorter display sizes the most common display sizes are 6, 8, 12, and 14. When you create a table, you can specify anyThe defined column length of 0 or greater than 14 will be forced to define column length 14. The odd numeric dimensions of the column length in the range from 1~13 are coerced to the next larger even number.  * column such as: * Define field length mandatory field length TIMESTAMP (0), TIMESTAMP (+) TIMESTAMP (+), TIMESTAMP (+) TIMESTAMP (1), TIMESTAMP (2) TIMESTAMP (5)-TIMESTAMP (6)   All TIMESTAMP columns have the same storage size, using the full precision (14-bit) of the specified time-period value to store a valid value regardless of the display size. Illegal date, will be forced to 0 storage * This has several meanings: *  www.2cto.com  1, although you define the column timestamp (8) When you build the table, but when you do data insertion and update timestamp column In fact, 14 bits of data are saved (including date and time), but when you make a query, MySQL returns you with 8-bit date data. If you use ALTER TABLE to widen a narrow timestamp column, information that was previously "hidden" will be displayed. 2. Similarly, narrowing a timestamp column does not result in information loss, except when the sense value is displayed, less information is displayed. 3, although the TIMESTAMP value is stored as full precision, the only function that directly manipulate the stored value is Unix_timestamp (); Because the column value of the MySQL return TIMESTAMP column is the value that is retrieved after the formatted This means that you may not be able to use certain functions to manipulate timestamp columns (such as hour () or second ()) unless the relevant part of the timestamp value is included in the formatted value. For example, if a timestamp column is defined above timestamp (10), the HH portion of the timestamp column is displayed, so using hour () on a shorter timestamp value produces an unpredictable result. 4, the illegal timestamp value is transformed to the appropriate type of "0" value (00000000000000). (datetime,date) * You can use the following statements to verify: *create TABLE Test (' ID ' INT (3) UNSIGNED auto_increment, ' Date1 ' TIMESTAMP (8) PRIMARY KEY (' id ')); Nsert into Test SET id = 1; SELECT * FROM test;+----+----------------+| ID |　Date1 |+----+----------------+| 1 | 20021114 |+----+----------------+alter TABLE test change ' date1 ' Date1 ' TIMESTAMP (14); SELECT * FROM test;+----+----------------+| ID |　Date1 |+----+----------------+| 1 | 20021114093723 |+----+----------------+  You can use the timestamp column type to automatically mark an INSERT or update operation with the current date and time. If you have more than one timestamp column, only the first one is automatically updated. Automatic Update the first timestamp column occurs under any of the following conditions: 1, the column value is not explicitly specified in an INSERT or load DATA infile statement. 2. Column values are not explicitly specified in an UPDATE statement and some other columns change values. (Note An update setting that lists the values it already has, which will not cause the timestamp column to be updated, because if you set a list as its current value, MySQL ignores the changes for efficiency.) 3. You explicitly set timestamp as NULL.4, except the first timestamp column can also be set to the current date and time, as long as the column is set to NULL, or now (). CREATE TABLE Test ( www.2cto.com   ' id ' INT (3) UNSIGNED auto_increment, ' date1 ' TIMESTAMP (+), ' Date2 ' TIMESTAMP (+), PRIMARY KEY (' id '));  insert into Test (ID, date1, date2) VALUES (1, NULL, NULL); INSERT into Test SET id= 2;+---- +----------------+----------------+| ID | Date1 |　Date2 |+----+----------------+----------------+| 1| 20021114093723 |　20021114093723 | | 2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+-> The first instruction is set date1, Date2 is null, so date1, date2 values are the current time the second instruction Because there are no date1, date2 column values, the first timestamp column date1 is updated to the current time, and two timestamp columns Date2 changed to "00000000000000" Update test SET id= 3 WHERE The date is illegal id=1;+----+----------------+----------------+| ID | Date1 |　Date2 |+----+----------------+----------------+| 3 | 20021114094009 |　20021114093723 | | 2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+-> This directive does not explicitly set Date2 column values, so the first timestamp column date1 will be updated to the current time  update Test SET id= 1,date1=date1,date2=now () WHERE id=3;  www.2cto.com  +----+----------------+----------------+| ID | Date1 |　Date2 |+----+----------------+----------------+| 1 | 20021114094009 |　20021114094320 | | 2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+-> This instruction is set date1=date1, so date1 column values do not change when updating data and are set Date2 =now (), so the Date2 column value is updated to the current time when the data is updated this directive is equivalent to the update test SETid= 1,date1=date1,date2=null WHERE id=3; You can use the Date_fromat () function to format the TIMESTAMP column select Id,date because the TIMESTAMP returned by MySQL is listed as a digital display _format (date1, '%y-%m-%d%h:%i:%s ') as Date1,date_format (Date2, '%y-%m-%d%h:%i:%s ') as Date2 from test;+----+---------- -----------+---------------------+| ID | Date1 |　Date2 |+----+---------------------+---------------------+| 1 | 2002-11-14 09:40:09 |　2002-11-14 09:43:20 | | 2 | 2002-11-14 09:37:24 | 0000-00-00 00:00:00 |+----+---------------------+---------------------+ select id,date_format (date1, '%y-%m-% d ') as Date1,date_format (Date2, '%y-%m-%d ') as Date2 from test;  www.2cto.com  +----+-------------+---------- ---+| ID | Date1 |　Date2 |+----+-------------+-------------+| 1 | 2002-11-14 |　2002-11-14 | | 2 | 2002-11-14 | 0000-00-00 |+----+-------------+-------------+  in a way, you can assign a value of a date type to an object of a different date type. However, it is particularly important to note that there may be some change in value or loss of information:  1, if you assign a date value to a datetime or timestamp object, the time portion of the resulting value is set to ' 00:00:00 ',　　Because time information is not included in the date value. 2. If you set a datetime or TThe Imestamp value is assigned to a Date object, and the time portion of the resulting value is deleted because the date type does not store time information. 3. Although datetime, date and timestamp values can all be specified with the same set of formats, all types do not have the same range of values. For example, the timestamp value cannot be more than 1970 or 2037 nights, which means that a date such as ' 1968-01-01 ' is legal when it is a datetime or date value, but it is not a correct timestamp value! And if such an object is assigned to the timestamp column, it will be transformed to 0.  www.2cto.com   * when specifying a date value, beware of certain flaws: * 1, a loose format that allows a value to be specified as a string can be spoofed. For example, because the ":" Delimiter is used, the value ' 10:11:12 ' may look like a time value, but if used in a date, the context will be interpreted as ' 2010-11-12 ' as the year. The value ' 10:45:15 ' will be transformed to ' 0000-00-00 ' because ' 45 ' is not a valid month.  2, the year value specified in 2 digits is ambiguous, because century is unknown. MySQL uses the following rules to interpret the 2-bit year value: The year value in the 00-69 range is transformed to 2000-2069. The year value in the range 70-99 is transformed to 1970-1999.

SQL Server timestamp timestamp