How to store time in MySQL database

1. Remember not to use strings to store dates

Strings take up more space!

The comparison efficiency of dates stored in strings is relatively low (comparison character by character), and cannot be calculated and compared using date-related APIs.

2. Choosing between Datetime and Timestamp

Datetime and Timestamp are two similar data types for storing time provided by MySQL.

How to choose between them?

Usually we prefer Timestamp

2.1 DateTime type has no time zone information

DateTime type has no time zone information (time zone is irrelevant)

When your After the time zone is changed, for example, if your server changes the address or the client connection time zone setting is changed, it will cause the time you read from the database to be incorrect.

Timestamp is related to time zone.

The value of the Timestamp type field will change with the change of the server time zone and will be automatically converted to the corresponding time. To put it simply, in different time zones, the value of this field will be different for the same record queried.

Let’s give a practical demonstration below!

Create table SQL statement:

CREATE TABLE `time_zone_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NULL,
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert data:

INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());

View data:

select dsqlate_time,time_stamp from time_zone_test;

Result:

-------------------------- ---------------------
| date_time                                                          time_stamp |
--------------------- ---------------------
| 2020-01-11 09:53:32 | 2020-01-11 09:53:32 |
-------------------------- --- ------------------

Modify the time zone of the current session:

set time_zone='+8:00';

View the data again:

--------------------- ---------------------
| date_time | time_stamp |
--------------------- ---------------------
| 2020-01-11 09:53:32 | 2020-01-11 17:53:32 |
-------------------------- -- -------------------

Extension: Some common sql commands about MySQL time zone settings

1. View the current session time zone

    SELECT @@session.time_zone;

2. Set the current session time zone

    SET time_zone = 'Europe/Helsinki';
    SET time_zone = "+00:00";

3. Database global time zone setting

    SELECT @@global.time_zone;

4. Set the global time zone

    SET GLOBAL time_zone = '+8:00';
    SET GLOBAL time_zone = 'Europe/Helsinki';

2.2 The DateTime type consumes more space

Timestamp only needs to use 4 bytes of storage space, but DateTime needs to consume 8 bytes of storage space. However, this approach also raises the issue that Timestamp has a more limited time range.

• DateTime ：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

• Timestamp： 1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

Timestamp in different versions of MySQL There are subtle differences.

3. Let’s look at the storage space of the MySQL date type again

The following figure shows the storage space occupied by the date type in the MySQL 5.6 version:

It can be seen that MySQL after 5.6.4 has an extra decimal place that requires 0 to 3 bytes. DataTime and Timestamp will occupy different storage space when stored.

For the sake of convenience, in this article we still default that Timestamp only requires 4 bytes of storage space, but DateTime requires 8 bytes of storage space.

4. Is numeric timestamp a better choice?

Many times, we will also use int or bigint type values, that is, timestamps, to represent time.

This storage method has some advantages of the Timestamp type, and using it to perform date sorting and comparison operations will be more efficient, and it is also very convenient across systems. After all, it is just a stored value. The disadvantage is also obvious, that is, the readability of the data is too poor, and you cannot intuitively see the specific time.

The definition of timestamp is as follows:

The definition of timestamp is counting from a base time, which is "1970-1-1 00:00:00 0:00", starting from this time, expressed as an integer, measured in seconds, this time integer continues to increase as time goes by. In this way, I only need one value to perfectly represent time, and this value is an absolute value, that is, no matter where you are in any corner of the earth, the timestamp representing time is the same, generated The values ​​are all the same, and there is no concept of time zone, so no additional conversion is required during the transmission of time in the system. It is only converted to local time in string format when displayed to the user.

Actual operations in the database:

mysql> select UNIX_TIMESTAMP('2020-01-11 09:53:32');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-01-11 09:53:32') |
+---------------------------------------+
|                            1578707612 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1578707612);
+---------------------------+
| FROM_UNIXTIME(1578707612) |
+---------------------------+
| 2020-01-11 09:53:32       |
+---------------------------+
1 row in set (0.01 sec)

5. Summary

Timestamp is recommended because the numerical representation of time is not intuitive enough

Every method is Each has its own advantages, and the best way to do it is based on the actual scenario. Let’s make a simple comparison of these three methods to help you choose the correct data type for storage time in actual development:

6.设置mysql date类型字段默认值支持0000格式

[mysqld]
sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

The above is the detailed content of How to store time in MySQL database. For more information, please follow other related articles on the PHP Chinese website!