MySQL timestamp type and Time Zone instance details, mysqltimestamp

MySQL timestamp type and Time Zone instance details, mysqltimestamp

MySQL timestamp type and Time Zone

Timestamp type time range of MySQL: between '2017-01-01 00:00:01 'and '2017-01-19 03:14:07 ', if the value exceeds this range, the record is '2017-00-00 00:00:00 '. An important feature of this type is that the storage time is closely related to the time zone, the Time range mentioned above is the Universal Time Coordinated standard, which refers to the standard Time at 0 degrees of longitude, in our daily life, the time zone uses the eastern hemisphere of the capital Beijing as the benchmark, and uses the eastern 8 Zone Time (commonly known as Beijing Time), which is 8 hours earlier than UTC, the Time Zone settings of the server also comply with this standard, so the time range corresponding to the timestamp should be calibrated to '2017-01-01 08:00:01 'and '2017-01-19 11:14:07 ', that is to say, 08:00:01 In the GMT + 8 is equivalent to 00:00:01 in UTC.

Note that the time of the timestamp type is not only related to the time zone when the record is written, but also to the time zone when it is 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> insert 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 displayed date varies according to the time zone, which is unique to the timestamp type in the MySQL date type.

If the value inserted into the timestamp column exceeds the specified range, the actual saved value is '2017-00-00 00:00:00 ', and a warning message is triggered:

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> insert 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)

The warning information triggered is only a warning at the MySQL level, not an error. The try catch function of the front-end application cannot be captured. However, due to the fact that the data actually written is not the expected value, some hidden risks may be hidden, once these risks are revealed, they may trigger exceptions in front-end applications.

For the timestamp type, you must understand the concept of time zone in actual applications. When setting the default value of the timestamp column and the actual value assignment, you must specify the actual storage status of the written value to avoid embedding risks. For existing records with errors, you can consider batch UPDATE and table structure modification.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!