The difference and usage of datetime and timestamp in MySQL

1. How to represent the current time in MySQL?

In fact, there are many ways to express it, 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. The date part corresponds to the "YYYY-MM-DD" format, and the time part corresponds to the "HH:MM:SS[.fraction]" format. For the date field, it only supports the date part. If the time part is inserted, it will discard the content of that part and prompt a 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 first one does not prompt a warning because its time part is 0

Similarities between TIMESTAMP and DATETIME:

1> Both can be used to represent dates of the type YYYY-MM-DD HH:MM:SS[.fraction].

The differences between TIMESTAMP and DATETIME are:

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 (Coordinated Universal Time) for storage. When querying, it is converted into the client's current time zone and returned.

For DATETIME, no changes are made and it is basically input and output as is.

Next, let's verify

First, create two test tables, one using timestamp format and one using 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 the same.

Secondly, change 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 "CST" above 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

The results show that the time returned in test is 8 hours earlier, while the time in test1 remains unchanged. This fully verifies the difference between the two.

2> The time range that can be stored by the two is different

The time range that timestamp can store is: '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'.

The time range that datetime can store is: '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'.

Summary: TIMESTAMP and DATETIME are not much different except for their storage range and storage method. Of course, TIMESTAMP is more appropriate 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)

Does it look a bit strange? I did not insert the hiredate field, and its value was automatically changed to the current value. Moreover, when creating the table, I did not define the "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" shown in the "show create table test\G" result.

In fact, this feature is Automatic Initialization and Updating.

Automatic initialization means that if there is no explicit assignment to the field (such as the hiredate field in the above example), it is automatically set to the current system time.

Automatic update means that if other fields are modified, the value of this field will be automatically updated to the current system time.

It is related to the "explicit_defaults_for_timestamp" parameter.

By default, the value of this parameter is OFF, as shown below:

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 official description below:

By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.

Many times, this is not what we want. How to disable it?

1. Set the value of "explicit_defaults_for_timestamp" to ON.

2. The value of "explicit_defaults_for_timestamp" is still OFF. There are two ways to disable it.

1> Use the DEFAULT clause to specify a default value for the column

2> Specify the NULL attribute for the 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 5.6.5, Automatic Initialization and Updating was applicable only to TIMESTAMP, and at most one TIMESTAMP field in a table was allowed to use this feature. As of MySQL 5.6.5, Automatic Initialization and Updating applies to both TIMESTAMP and DATETIME values, with no limit on the number of values.

refer to:

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

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.