How to store time in database

Time:2021-4-16

1. Don’t use string to store date

I remember doing this when I was in University, and now many novices who don’t know much about databases do the same. It can be seen that this method of storing dates has some advantages, that is, it is simple and straightforward, and easy to use.

However, this is not correct. There are two main problems

  1. String takes up more space!
  2. The date comparison efficiency of string storage is relatively low (character by character comparison), and can not be calculated and compared by date related API.

2. The choice between datetime and timestamp

Datetime and timestamp are two data types with similar save time provided by mysql. How to choose between them?

Usually we prefer timestamp.Let’s talk about why!

2.1 datetime type has no time zone information

Datetime type has no time zone information (time zone independent)The time saved by datetime type is the time corresponding to the time zone set by the current session. What would be the problem? When your time zone is changed, for example, when your server changes its address or the client changes its time zone settings, it will cause the time you read out from the database to be wrong. Don’t underestimate this problem. Many systems make a lot of jokes because of this problem.

Timestamps are about time zones. The value of the timestamp type field will change with the time zone of the server, and it will be automatically converted to the corresponding time. To put it simply, the value of this field will be different when the same record is queried in different time zones.

The following actual demonstration!

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 date_time,time_stamp from time_zone_test;

result:

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

Now let’s run it

To 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: a common SQL command about MySQL time zone setting

#View current session time zone
SELECT @@session.time_zone;
#Set current session time zone
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
#Database global time zone setting
SELECT @@global.time_zone;
#Set global time zone
SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';

2.2 datetime type consumes more space

Timestamp only needs 4 bytes of storage space, but datetime needs 8 bytes of storage space. However, this also causes a problem, the timestamp represents a smaller 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

There are subtle differences between different versions of MySQL.

3 look at the storage space of MySQL date type again

The following figure shows the storage space of date type in MySQL version 5.6
How to store time in database

It can be seen that MySQL after 5.6.4 needs an extra decimal place of 0-3 bytes. DataTime and timestamp have several different storage footprint.

For convenience, in this paper, we still default that timestamp only needs 4 bytes of storage space, but datetime needs 8 bytes of storage space.

4. Is numerical timestamp a better choice?

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

This kind of storage method has some advantages of the timestamp type, and the efficiency of using it to sort and compare dates will be higher, and cross system is also very convenient, after all, it is only stored values. The disadvantage is also very obvious, that is, the readability of the data is too poor, you can’t see the specific time intuitively.

The definition of time stamp is as follows:

The definition of time stamp starts from a base time, which is “1970-1-1 00:00:00 + 0:00”. Starting from this time, it is expressed as an integer and timed in seconds. With the passage of time, this time integer increases continuously. In this way, I only need a value to perfectly represent the time, and this value is an absolute value, that is, no matter where I am on the earth, the time stamp representing the time is the same, the generated values are the same, and there is no concept of time zone, so in the transmission of time in the system, there is no need for additional rotation In other words, only when it is displayed to the user can it be converted to the local time in string format.

Actual operation in 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

How to store time in MySQL? Datetime? Timestamp? The time stamp of the saved value?

It seems that there is no silver bullet. Many programmers think that numerical timestamps are really good, efficient and compatible, but many people think that they are not intuitive enough. To put it in here, the author of high performance mysql recommends timestamp because the time of numerical expression is not intuitive enough. The following is the original text:

How to store time in database

Each way has its own advantages, according to the actual scene is the king. Next, we will make a simple comparison of these three methods for you to choose the right data type for storage time in the actual development
How to store time in database