MySQL timestamp type and time zone instance detailed _mysql

MySQL timestamp type and time zone

MySQL's timestamp type time range between ' 1970-01-01 00:00:01 ' and ' 2038-01-19 03:14:07 ', exceeding this range the value is recorded as ' 0000-00-00 00:00:00 ', an important feature of this type is that the time that is saved is closely related to the timezone, which is the UTC (Universal Time coordinated) standard, which refers to the standard time of 0 degrees longitude, China's daily life time zone with the capital Beijing in the 8th area of the Old world as the benchmark, the unified use of the East 8 area time (commonly known as Beijing Time), 8 hours earlier than UTC, the server's time zone is set to comply with this standard, so the time range corresponding to the timestamp should be calibrated to 08:00:01 ' and ' 2038-01-19 11:14:07 ', that is to say, the East eight area of 1970-1-1 08:00:01 is equivalent to UTC 1970-1-1 00:00:01.

Note that the timestamp type is not only relevant to the time zone when the record was written, but also to the time zone when it was displayed, for example:

mysql> desc J1_DT; +-------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default |
Extra | +-------+-----------+------+-----+-------------------+-------+
| DT | Timestamp |   NO | |    Current_timestamp | 
|  +-------+-----------+------+-----+-------------------+-------+ 1 row in Set (0.00 sec) mysql> inserts into J1_DT values
(' 1970-01-01 08:00:01 ');
Query OK, 1 row Affected (0.00 sec) mysql> select * from J1_dt; +---------------------+
|
DT | +---------------------+
| 
1970-01-01 08:00:01 |
+---------------------+ 1 row in Set (0.00 sec) mysql> set time_zone= ' +0:00 ';
Query OK, 0 rows Affected (0.00 sec) mysql> select * from J1_dt; +---------------------+
|
DT | +---------------------+
| 
1970-01-01 00:00:01 |
+---------------------+ 1 row in Set (0.00 sec) mysql> set time_zone= ' +1:00 ';
Query OK, 0 rows Affected (0.00 sec) mysql> select * from J1_dt; +---------------------+
|
DT | +---------------------+
| 
1970-01-01 01:00:01 |
 +---------------------+ 1 row in Set (0.00 sec)

As shown above, the date displayed varies according to the time zone, which is the unique time zone feature of the timestamp type in the MySQL date type.

If the value inserted into the timestamp type column is outside the specified range, the actual saved value is ' 0000-00-00 00:00:00 ' and triggers a warning message:

 mysql> set time_zone= ' +8:00 ';
Query OK, 0 rows Affected (0.00 sec) mysql> select * from J1_dt; +---------------------+
|
DT | +---------------------+
| 
1970-01-01 08:00:01 |
+---------------------+ 1 row in Set (0.00 sec) mysql> inserts into J1_DT values (' 1970-01-01 00:00:01 ');
Query OK, 1 row affected, 1 Warning (0.00 sec) mysql> Show warnings; +---------+------+------------------------------------------------------+
| Level | Code |
message | +---------+------+------------------------------------------------------+
| Warning | 1264 | 
Out of range value adjusted for column ' dt ' at row 1 | +---------+------+------------------------------------------------------+ 1 row in Set (0.00 sec) mysql> SELECT *
From J1_dt; +---------------------+
|
DT | +---------------------+
| 
1970-01-01 08:00:01 | | 
0000-00-00 00:00:00 | +---------------------+ 2 rows in Set (0.00 sec) 

Triggered warning messages at the MySQL level is only a warning rather than an error, the front-end application of Try catch is not captured, however, because the actual written data is not expected, it is possible to bury some hidden dangers, once exposed, it is possible to trigger the front-end application exception.

For timestamp type, it is important to understand the concept of time zone in practical application, when setting the default value of timestamp column, and when the value of the actual assignment must be clearly written to save the state, try to avoid embedding hidden trouble. For existing records that have already been faulted, you can consider handling them by batch update and modifying the table structure.

Thank you for reading, I hope to help you, thank you for your support for this site!