Details of mysql's TIMESTAMP usage

1. Variations of TIMESTAMP

TIMESTAMP timestamps can have multiple different features when they are created, such as:

1. Refresh the data column when creating new records and modifying existing records:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2. Set this field to the current time when a new record is created, but do not refresh it when it is modified in the future:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP

3. Set this field to 0 when creating a new record, and refresh it when modifying it:

TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

4. Set this field to the given value when the new record is created, and refresh it when it is modified later:

TIMESTAMP DEFAULT  ' yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP

MySQL currently does not support column Default as a function. For example, you can use the TIMESTAMP column type if the default value for one of your columns is the current update date and time

2. TIMESTAMP column type

The TIMESTAMP value can be displayed digitally from some time in 1970 to 2037, with an accuracy of 1 second.
The format of the TIMESTAMP value display size is shown in the following table:
:

+---------------+----------------+
|  The 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　　　　　　 |
+---------------+----------------+

The "full" TIMESTAMP format is 14-bit, but the TIMESTAMP column can also be used with shorter display sizes, creating the most common display sizes of 6, 8, 12, and 14.
You can specify an arbitrary display size when creating a table, but defining a column length of 0 or greater than 14 will force you to define a column length of 14.
The column length is forced to the next larger even number in the odd-value size range from 1 to 13.

Such as:

 Define field length   Mandatory field length 
TIMESTAMP(0) ->　TIMESTAMP(14)
TIMESTAMP(15)->　TIMESTAMP(14)
TIMESTAMP(1) ->　TIMESTAMP(2)
TIMESTAMP(5) ->　TIMESTAMP(6)

All TIMESTAMP columns have the same storage size, using the full precision (14 bits) of the specified period time value to store legal values regardless of display size. Invalid dates will be forced to be stored at 0

This has several implications:

1. Although you defined column TIMESTAMP(8) when you built the table, the TIMESTAMP column actually holds 14 bits of data (including minutes and seconds of month, year and day) when you inserted and updated the data, except that MySQL returns you 8 bits of month, year and day data when you query. If you use ALTER TABLE to widen a narrow TIMESTAMP column, information previously "hidden" will be displayed.

2. Similarly, shrinking an TIMESTAMP column does not cause information to be lost, except that less information is displayed when the value is displayed.

3. Although the TIMESTAMP value is stored with full accuracy, the only function that directly operates on the stored value is UNIX_TIMESTAMP(); Since MySQL returns the value of the TIMESTAMP column as a formatted retrieved value, this means that you may not be able to use certain functions to manipulate the TIMESTAMP column (such as HOUR() or SECOND()) unless the relevant portion of the TIMESTAMP value is included in the formatted value.
For example, the HH part of the TIMESTAMP column is displayed only if 1 TIMESTAMP column is defined above TIMESTAMP(10), so using HOUR() on a shorter TIMESTAMP value produces an unpredictable result.

4. The illegal TIMESTAMP value is converted to the appropriate type of "zero" value (00000000000000). (DATETIME,DATE)

For example, you can use the following statement to verify:

CREATE TABLE test ('id' INT (3) UNSIGNED AUTO_INCREMENT, 'date1'
TIMESTAMP (8) PRIMARY KEY('id'));
INSERT 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 INSERT or UPDATE operations with the current date and time.
If you have multiple TIMESTAMP columns, only the first is automatically updated. Automatic updating of 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 one UPDATE statement and the other column values change. (Note that 1 UPDATE sets 1 column to the value it already has. This will not cause the TIMESTAMP column to be updated, because if you set 1 column to its current value, MySQL ignores the change for efficiency.)
3. You explicitly list TIMESTAMP as NULL.
4. The TIMESTAMP column other than the first can also be set to the current date and time as long as the column is set to NULL, or NOW().

CREATE TABLE test ( 
'id' INT (3) UNSIGNED AUTO_INCREMENT,
'date1' TIMESTAMP (14),
'date2' TIMESTAMP (14),
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 |
+----+----------------+----------------+

Instruction 1 sets date1 and date2 as NULL, so the values of date1 and date2 are the current time. Instruction 2 sets THE values of date1 and date2 as the current time, the first TIMESTAMP column date1 is updated to the current time, and the two TIMESTAMP columns date2 become "00000000000000" because the date is illegal.

UPDATE test SET id= 3 WHERE id=1;
+----+----------------+----------------+
| id | date1　　　　　| date2　　　　　|
+----+----------------+----------------+
|　3 | 20021114094009 | 20021114093723 |
|　2 | 20021114093724 | 00000000000000 |
+----+----------------+----------------+

This directive does not explicitly set the column value for date2, 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; 
+----+----------------+----------------+
| id | date1　　　　　| date2　　　　　|
+----+----------------+----------------+
|　1 | 20021114094009 | 20021114094320 |
|　2 | 20021114093724 | 00000000000000 |
+----+----------------+----------------+

This instruction sets date1=date1, so the value of date1 column will not change when updating the data. However, since date2=NOW() is set, the value of date2 column will be updated to the current time when updating the data. This instruction is equivalent to:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP

0

Since MySQL returns a numeric display of TIMESTAMP columns, you can use the DATE_FROMAT() function to format the TIMESTAMP column as follows:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP

1

At some point, you can assign a value of one date type to an object of a different date type.
However, it is important to note that there may be 1 change in value or loss of information:

1. If you assign an DATE value to an DATETIME or TIMESTAMP object, the time portion of the result value is set to '00:00:00' because the DATE value does not contain time information.
2. If you assign an DATETIME or TIMESTAMP value to an DATE object, the time portion of the resulting value is deleted because the DATE type does not store time information.
3. Although the DATETIME, DATE and TIMESTAMP values can all be specified with the same formatset, all types do not have the same value range.

For example, the TIMESTAMP value cannot be earlier than 1970 or later than 2037, which means that a date such as '1968-01-01' is valid as an 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.

3. When specifying date values, beware of certain defects:

1. Loose formatting that allows values to be specified as strings can be spoofed. For example, because of the use of the ':' separator, the value '10:11:12' might look like a time value, but if used in 1 date, the context would be interpreted as '2010-11-12'. The value '10:45:15' will be converted to '0000-00-00' because '45' is not a legal month.

2. The 2-digit annual value is ambiguous because the century is unknown. MySQL interprets 2-digit annual values using the following rules:
Annual values in the range 00-69 are converted to 2000-2069. Annual values in the range 70-99 are converted to 1970-1999.

PS: Here we recommend a timestamp conversion tool for Unix on this site, with the operation method of timestamp in various languages, including PHP, MySQL, SQL Server, java and other timestamp acquisition and conversion skills:

Unix timestamp (timestamp) conversion tool: http: / / tools ofstack. com/code/unixtime