Difference and Application of datetime and timestamp in MySQL
- 2021-12-13 17:31:53
- OfStack
1. How is the current time represented in MySQL?
In fact, there are quite a few expressions, which are summarized as follows:
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
NOW()
LOCALTIME
LOCALTIME()
LOCALTIMESTAMP
LOCALTIMESTAMP()
2. Comparison between TIMESTAMP and DATETIME
A complete date format is as follows: YYYY-MM-DD HH: MM: SS [. fraction], which can be divided into two parts: date part and time part, where date part corresponds to "YYYY-MM-DD" in the format and time part corresponds to "HH: MM: SS [. fraction]" in the format. For the date field, it only supports the date section, and if the contents of the time section are inserted, it discards the contents of that section and prompts for an warning.
As shown below:
mysql> create table test(id int,hiredate date);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values(1,'20151208000000');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(1,'20151208104400');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warning;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1
mysql> select * from test;
+------+------------+
| id | hiredate |
+------+------------+
| 1 | 2015-12-08 |
| 1 | 2015-12-08 |
+------+------------+
2 rows in set (0.00 sec)
Note: The reason why the first one doesn't prompt warning is that its time part is all 0
Similarities between TIMESTAMP and DATETIME:
1 > Both can be used to indicate dates of type YYYY-MM-DD HH: MM: SS [. fraction].
Differences between TIMESTAMP and DATETIME:
1 > The storage methods of the two are different
For TIMESTAMP, it converts the time inserted by the client from the current time zone to UTC (Universal Mean Time) for storage. When querying, it is converted into the current time zone of the client and returned.
For DATETIME, there is no change, and it is basically the same input and output.
Next, let's verify 1
First, create two test tables, one in timestamp format and one in datetime format.
mysql> create table test(id int,hiredate timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values(1,'20151208000000');
Query OK, 1 row affected (0.00 sec)
mysql> create table test1(id int,hiredate datetime);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 values(1,'20151208000000');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+---------------------+
| id | hiredate |
+------+---------------------+
| 1 | 2015-12-08 00:00:00 |
+------+---------------------+
1 row in set (0.01 sec)
mysql> select * from test1;
+------+---------------------+
| id | hiredate |
+------+---------------------+
| 1 | 2015-12-08 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)
The output of both is 1 sample.
Second, modify the time zone of the current session
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
mysql> set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+------+---------------------+
| id | hiredate |
+------+---------------------+
| 1 | 2015-12-07 16:00:00 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> select * from test1;
+------+---------------------+
| id | hiredate |
+------+---------------------+
| 1 | 2015-12-08 00:00:00 |
+------+---------------------+
1 row in set (0.01 sec)The above-mentioned "CST" refers to the system time of the host where MySQL is located, which is the abbreviation of China Standard Time, China Standard Time UT+8: 00
It can be seen from the results that the time returned in test is 8 hours earlier, while the time in test1 is unchanged. This fully verifies the difference between the two.
2 > The time range that can be stored by the two is different
The storage time range of timestamp is from '1970-01-01 00:00: 01.000000' to '2038-01-19 03:14: 07.999999'.
The storage time range of datetime is from '1000-01-01 00:00: 000000' to '9999-12-31 23:59: 59.999999'.
Summary: TIMESTAMP and DATETIME are not much different except for the storage range and storage mode. Of course, TIMESTAMP is more suitable for cross-time zone business.
3. Automatic initialization and update of TIMESTAMP and DATETIME
First, let's look at the following operations
mysql> create table test(id int,hiredate timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test(id) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+---------------------+
| id | hiredate |
+------+---------------------+
| 1 | 2015-12-08 14:34:46 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`hiredate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)It seems strange that I didn't insert the hiredate field, its value was automatically modified to the current value, and when I created the table, I didn't define "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" displayed in the result of "show create table test\ G".
In fact, this feature is automatic initialization and automatic update (Automatic Initialization and Updating).
Automatic initialization means automatically setting to the current system time if there is no explicit assignment to the field, such as the hiredate field in the previous example.
Automatic update means that if other fields are modified, the value of that field is automatically updated to the current system time.
It relates to the "explicit_defaults_for_timestamp" parameter.
By default, the value of this parameter is OFF, as follows:
mysql> show variables like '%explicit_defaults_for_timestamp%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
Let's take a look at the instructions in the official file:
By default, the first TIMESTAMP column has both DEFAULT CURRENT _ TIMESTAMP and ON UPDATE CURRENT neither is specified explicitly.
Most of the time, this is not what we want. How can we disable it?
1. Set the value of "explicit_defaults_for_timestamp" to ON.
2. The value of "explicit_defaults_for_timestamp" is still OFF, and there are two ways to disable it
1 > Specify a default value for this column with the DEFAULT clause
2 > Specify the NULL property for this column.
As shown below:
mysql> create table test1(id int,hiredate timestamp null);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) DEFAULT NULL,
`hiredate` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> create table test2(id int,hiredate timestamp default 0);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test2\G
*************************** 1. row ***************************
Table: test2
Create Table: CREATE TABLE `test2` (
`id` int(11) DEFAULT NULL,
`hiredate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Prior to MySQL version 5.6. 5, Automatic Initialization and Updating was only applicable to TIMESTAMP, and this feature was allowed for up to one TIMESTAMP field in one table. Starting from MySQL 5.6. 5, Automatic Initialization and Updating is applicable to both TIMESTAMP and DATETIME, and there is no limit to the number.
Reference:
1. http://dev.mysql.com/doc/refman/5.6/en/datetime.html
2. http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html