DATETIME and TIMESTAMP are common date/time data types in MySQL. Among them, DATETIME and TIMESTAMP are similar in appearance, but there are many differences in essence. When it comes to timezone, there are some differences.
___________ format
The date/time format in MySQL can be either string or numeric, depending on the context. If the expected input in the context is a date type, ‘2015-07-21’, ‘20150721’, and 20150721 are all interpreted as date types.
In standard SQL, the date/time type must be specified by a type keyword and a string.
DATE 'str'
TIME 'str'
TIMESTAMP 'str'
Copy the codeMySQL inherits the standard SQL specification, but also makes some extensions. First, there is no need to specify a type keyword in MySQL; In addition, MySQL recognizes the ODBC specification that corresponds to the standard SQL specification.
{ d 'str' }
{ t 'str' }
{ ts 'str' }
Copy the codeIn MySQL, the ‘TIMESTAMP’ syntax finally generates a value of type ‘DATETIME’, Because the ‘DATETIME’ range in MySQL is closer to the ‘TIMESTAMP’ type in standard SQL, ‘TIMESTAMP’ type from 0001 to 9999 in standard SQL)
4. SharedDATETIME 和 TIMESTAMPThe format of:
-
The value is in the format of ‘YYYY-MM-DD hh: MM: SS ‘or ‘YY-MM-DD hh: MM :ss’. Where any punctuation mark can be used as a date or time separator, For example, ‘2012-12-31 11:30:45’, ‘2012^12^31 11+30+45’, ‘2012/12/31 11*30*45’, and ‘2012@12@31 11^30^45’ are all equivalent. In addition, the delimiter between the date and time can be a space or T, for example, ‘2012-12-31 11:30:45’ and ‘2012-12-31T11:30:45’ are equivalent.
-
String format ‘YYYYMMDDhhmmss’ or ‘YYMMDDhhmmss’ without delimiters. For example, ‘20070523091528’ and ‘070523091528’ will be interpreted as ‘2007-05-23 09:15:28’, but ‘071122129015’ will be interpreted as ‘0000-00-00 00:00:00′, Because ’90’ is not a valid number of hours.
-
The value type is YYYYMMDDhhmmss or YYMMDDhhmmss.
Annual meetings between 1. 70-99 and 2. 00-69 are interpreted as 1970-1999 and annual meetings between 2
In the string format, if the value of month, date, hour, minute, or second is less than 10, do not prefix 0. For example, 2015-6-9 1:2:3 is interpreted as 2015-06-09 01:02:03.
For numeric formats, the length should be 6, 8, 12, or 14 bits. If the length is 8 or 14, the length of the year is 4 bits, otherwise the length of the year is 2 bits.
⓶ decimal seconds
The DATETIME and TIMESTAMP types in MySQL support fractional seconds with a maximum accuracy of 6 bits (microseconds). The syntax for defining fractional seconds is type_name(FSP).
CREATE TABLE t1 (
t TIMESTAMP(3),
dt DATETIME(6));Copy the codeWhen writing records with fractional seconds to a data table, if the precision of the field definition is less than the precision of the value being written, fractional seconds are rounded to the precision of the field definition by default. If the TIME_TRUNCATE_FRACTIONAL mode is enabled, fractional seconds are truncated to the precision of the field definition.
CREATE TABLE fractest(
c1 TIME(2),
c2 DATETIME(2),
c3 TIMESTAMP(2));//roundedINSERT INTO fractest VALUES('17:51:04. 777'.'the 2018-09-08 17:51:04. 777'.'the 2018-09-08 17:51:04. 777');
mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1 | c2 | c3 |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2018- 09- 08 17:51:04.78 | 2018- 09- 08 17:51:04.78 |
+-------------+------------------------+------------------------+
1 row in set (0.00 sec)
//The interceptionSET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL');
INSERT INTO fractest VALUES('17:51:04. 777'.'the 2018-09-08 17:51:04. 777'.'the 2018-09-08 17:51:04. 777');
mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1 | c2 | c3 |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2018- 09- 08 17:51:04.78 | 2018- 09- 08 17:51:04.78 |
| 17:51:04.77 | 2018- 09- 08 17:51:04.77 | 2018- 09- 08 17:51:04.77 |
+-------------+------------------------+------------------------+
2 rows in set (0.00 sec)
Copy the codeThe separator between decimal seconds and ‘DATETIME’ or ‘TIMESTAMP’ can only be ‘. ‘
Scope of 2.
In MySQL, regardless of the format in which DATETIME data is written, the final MySQL parse and display format is YYYY-MM-DD HH: MM: SS. The value ranges from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
For the ‘DATETIME’ type, dates and times earlier than ‘1000-01-01 00:00:00’ may also be written, although the range of support begins with ‘1000-01-01 00:00:00’ :
CREATE TABLE `time_format_test` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `dt` datetime NOT NULL.PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Time and date Format Test' mysql> insert into time_format_test (ts, dt) values (now(), '0800-01-01 00:00:00'); Query OK, 1 row affected (0.00 sec) mysql> select * from time_format_test; +----+---------------------+---------------------+ | id | ts | dt | +----+---------------------+---------------------+ | 1 | 2021- 11- 25 08:53:29 | 0800- 01- 01 00:00:00 | +----+---------------------+---------------------+ 1 row in set (0.00 sec) Copy the code
The range supported by TIMESTAMP in MySQL is much smaller than DATETIME, and the range is only UTC 1970-01-01 00:00:01 to UTC 2038-01-19 03:14:07.
Both ‘DATETIME’ and ‘TIMESTAMP’ types can contain decimal seconds, which are also stored when values containing decimal seconds are written to columns of both data types.
After including decimal seconds, the DATETIME ranges from 1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999. Accordingly, the range of TIMESTAMP becomes UTC 1970-01-01 00:00:01.000000 to UTC 2038-01-19 03:14:07.999999.
3. The time zone
In MySQL, a value of type TIMESTAMP is converted from the current time zone to UTC when saved and from UTC to the specified time zone when read. DATETIME values are stored and read regardless of time zone, and are stored as bigInt values underneath. By default, the time zone used by the MySQL server is the same as that used by the MySQL server. If the time zone is specified in the MySQL connection, the current time zone is the time zone specified in the MySQL connection.
CREATE TABLE `time_format_test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`dt` datetime NOT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Time and date Format Test'
//Time zone The default time zone is the same as that of the server mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
1 row in set (0.00 sec)
mysql> insert into time_format_test (ts, dt) values (now(), now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts | dt |
+----+---------------------+---------------------+
| 1 | 2021- 11- 25 10:05:46 | 2021- 11- 25 10:05:46 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
//Modify time zone Settings,TIMESTAMPThe value of DATETIME does not change mysql> set time_zone = '+ 0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| +00:00 |
+-------------+
1 row in set (0.00 sec)
mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts | dt |
+----+---------------------+---------------------+
| 1 | 2021- 11- 25 02:05:46 | 2021- 11- 25 10:05:46 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> set time_zone = From the '+';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| +08:00 |
+-------------+
1 row in set (0.00 sec)
mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts | dt |
+----+---------------------+---------------------+
| 1 | 2021- 11- 25 10:05:46 | 2021- 11- 25 10:05:46 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
Copy the codeThe value of the TIMESTAMP type changes with time zone, as does the related function FROM_UNIXTIME().
mysql> select unix_timestamp('the 2021-11-25 10:05:46');
+---------------------------------------+
| unix_timestamp('the 2021-11-25 10:05:46') |
+---------------------------------------+
| 1637805946 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> set time_zone = '+ 0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select from_unixtime(1637805946);
+---------------------------+
| from_unixtime(1637805946) |
+---------------------------+
| 2021- 11- 25 02:05:46 |
+---------------------------+
1 row in set (0.01 sec)
mysql> set time_zone = From the '+';
Query OK, 0 rows affected (0.00 sec)
mysql> select from_unixtime(1637805946);
+---------------------------+
| from_unixtime(1637805946) |
+---------------------------+
| 2021- 11- 25 10:05:46 |
+---------------------------+
1 row in set (0.00 sec)
Copy the codeStarting with MySQL 8.0.19, you can specify a time zone when writing a value of type DATETIME or TIMESTAMP. The date/time zone specified is converted to the current time zone of the database after being written. After that, if you change the time zone Settings, the TIMESTAMP type will change at any time, but the DATETIME type will not change at any time.
When specifying the time zone, if the time zone is smaller than 10, you must add a leading 0; otherwise, the written value will be invalid and become 0
//When setting the time zone, the value is less than10The time zone must be preceded0
mysql> insert into time_format_test (ts, dt) values ('the 2021-11-25 10:35:18 + 09:00'.'the 10:35:18 01:00 + 2021-11-25');
Query OK, 1 row affected (0.00 sec)
//Values set to the time zone will be converted to the current time zone value mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts | dt |
+----+---------------------+---------------------+
| 1 | 2021- 11- 25 09:35:18 | 2021- 11- 25 17:35:18 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> set time_zone = '+ 0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts | dt |
+----+---------------------+---------------------+
| 1 | 2021- 11- 25 01:35:18 | 2021- 11- 25 17:35:18 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
Copy the codeThe host has automatic initialization and automatic updates
For columns of the DATETIME and TIMESTAMP types, the column values can be automatically initialized and automatically updated by setting DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.
For columns with auto-update set, auto-fining does not work if other columns in the same row are updated but the value does not change.
mysql> update time_format_test set dt = 'the 2021-11-25 17:35:18' where id = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts | dt |
+----+---------------------+---------------------+
| 1 | 2021- 11- 25 09:35:18 | 2021- 11- 25 17:35:18 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> update time_format_test set dt = now() where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts | dt |
+----+---------------------+---------------------+
| 1 | 2021- 11- 25 11:19:40 | 2021- 11- 25 11:19:40 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
Copy the codeFor the TIMESTAMP type, if the system variable explicit_DEFAULts_FOR_TIMESTAMP has a value of 0 and the column value is not allowed to be NULL, the column value is automatically changed to the current date-time value when NULL is assigned to the corresponding column.
CREATE TABLE `time_format_test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`ts` timestamp,
`dt` datetime,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Time and date Format Test';
Query OK, 0 rows affected (0.05 sec)
mysql> select @@explicit_defaults_for_timestamp;
+-----------------------------------+
| @@explicit_defaults_for_timestamp |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> insert into time_format_test (ts, dt) values (null.null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from time_format_test;
+----+---------------------+------+
| id | ts | dt |
+----+---------------------+------+
| 1 | 2021- 11- 25 11:27:58 | NULL |
+----+---------------------+------+
1 row in set (0.00 sec)
Copy the codeFor the TIMESTAMP type, if the system variable explicit_DEFAULts_FOR_TIMESTAMP has a value of 0 and column values are not allowed to be NULL, DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP are automatically added to the first column of type TIMESTAMP.
CREATE TABLE `time_format_test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`ts1` timestamp,
`ts2` timestamp,
`dt` datetime,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Time and date Format Test';
Query OK, 0 rows affected (0.04 sec)
mysql> show create table time_format_test;
+------------------+----------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ --------------------------------+
| Table | Create Table |
+------------------+----------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ --------------------------------+
| time_format_test | CREATE TABLE `time_format_test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`dt` datetime DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Time and date Format Test' |
+------------------+----------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ --------------------------------+
1 row in set (0.00 sec)
Copy the codeIf you want to avoid this, you can manually set the default value for the TIMESTAMP type column or allow it to be NULL when defining the data table. In addition, you can set the system variable explicit_DEFAULts_FOR_TIMESTAMP to 1, which means that the default values for columns of type TIMESTAMP as well as automatic updates need to be specified explicitly.
If a column of type TIMESTAMP or DATETIME is defined with fractional seconds precision specified, the precision of fractional seconds in the same column should be the same throughout.
CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6));//The following definitions are not allowedCREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3));Copy the code