The difference between datatime and timestamp in MySQL

There are three date types in MySQL:

date(year-month-day)

create table test(hiredate date);

datetime (datetime type)

create table test(hiredate datetime)

timestamp (postmark type, saves year-month-day hour-minute-second)

create table test(hiredate timestamp)

Differences between datetime and timestamp

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.

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

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

mysql> show variables like '%explicit_defaults_for_timestamp%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF |
+---------------------------------+-------+
row in set (0.00 sec)

Summary: timestamp is automatically initialized and updated. When you update a record, the column value is automatically updated. This is the biggest difference from datatime.

Supplement: Some differences and problems between DATETIME and TIMESTAMP

I encountered a problem today. Although the data was updated, the update_time field still remained at the time when the data was created.

Normally, this field should be updated automatically.

Checked the table structure.

`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP

It is found that the type of the update_time field is datetime

This raises two questions: (1) the difference between timestamp and datetime; (2) why CURRENT_TIMESTAMP can be used for datetime types

The difference between timestamp and datetime

a) The default value of DATETIME is null; the default value of TIMESTAMP field is not null (not null), and the default value is the current time (CURRENT_TIMESTAMP). If no special processing is done and the update value of the column is not specified in the update statement, the default update is the current time.

This difference explains why we usually don't need to manage this field to update automatically, because most of the time we use timestamp; here we use datetime, and there is no mechanism to automatically update the current time, so we need to manually update this field in the upper layer.

b) DATETIME uses 8 bytes of storage space and TIMESTAMP uses 4 bytes of storage space. Therefore, TIMESTAMP is more space-efficient than DATETIME.

This difference explains why the timestamp type is used more often.

c) 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.

d) The time range that can be stored 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'.

Why can CURRENT_TIMESTAMP be used for datetime types?

In versions prior to MySQL 5.6, CURRENT_TIMESTAMP can only be used for timestamp types.

After version 5.6, CURRENT_TIMESTAMP can also be used for datetime types

I checked the database with select version() and found that the version is 5.6.29

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.