a commonplace talk of an old scholar! How does the database store time? Do you really know?

Time:2020-1-25

It is inevitable for us to save time in our development, for example, we need to record the time of this record in the operation table, record the transaction time of transfer, record the departure time, etc. You will find that this time is closely related to our development. Good or bad use will have a great impact on our business and even our functions. Therefore, it is necessary for us to start again and get to know this thing well.

This is a short and pithy article. Read it carefully and you will learn a lot!

1. Do not use string to store date

I remember that I did this when I was in University, and now many novices who don’t know much about database will also do this. Obviously, the advantage of this way of storing date is that it’s simple, straightforward and easy to use.

However, this is not the right way to do it. There are two main problems:

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

2. Choose between datetime and timestamp

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

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

2.1 datetime type without time zone information

Datetime type has no time zone information (time zone independent), the datetime type saves the time corresponding to the time zone set by the current session. What’s the problem? After changing your time zone, such as changing the address of your server or changing the time zone setting of the client connection, you will read the time error from the database. Don’t underestimate this problem. Many systems make a lot of jokes because of this problem.

Timestamp is related to time zone。 The value of the timestamp type field will change with the time zone of the server. It will be automatically converted to the corresponding time. In other words, the value of this field will be different when querying the same record in different time zones.

Let’s demonstrate it!

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 we run

Modify the time zone of the current session:

set time_zone='+8:00';

To 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 setting MySQL time zone

#View the current session time zone
SELECT @@session.time_zone;
#Set the current session time zone
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
#Database global time zone settings
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. Timestamps represent 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

Timestamp has subtle differences in different versions of MySQL.

3 see MySQL date type storage space again

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

a commonplace talk of an old scholar! How does the database store time? Do you really know?

You can see that MySQL after 5.6.4 has an extra decimal place that needs 0-3 bytes. DataTime and timestamp have several different storage footprint.

For convenience, in this article, we still use the default timestamp to use only 4 bytes of storage space, but datetime needs 8 bytes of storage space.

4. Is numerical time stamp a better choice?

In many cases, we also use int or bigint type values, which are time stamps, to represent time.

This storage method has some advantages of the timestamp type, and the efficiency of using it for date sorting and comparison will be higher, cross system is also very convenient, after all, it is only the stored value. The disadvantage is also obvious, that is, the readability of the data is so poor that you can’t see the specific time intuitively.

Timestamps are defined as follows:

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

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 value saving?

It seems that without a silver bullet, many programmers will think that the numerical time stamp is really good, efficient and compatible, but many people think it is not intuitive enough. Here’s a word. The author of “high performance mysql” is recommending timestamp, because the time of numerical representation is not intuitive enough. Here is the original:

<img style=”zoom:50%;” />

Each method has its own advantages. According to the actual scene, it is the king’s way. Let’s make a simple comparison of these three methods for you to choose the right data type of storage time in the actual development:

<img style=”zoom:50%;” />

If you have any questions, please leave me a message! If there are any questions in the article, please point out that brother guide is very grateful!

I will introduce the following articles:

  • [] Java 8’s support for dates and why simpledateformat cannot be used.
  • [] how to use it in springboot (JPA as an example)

Open source project recommendation

Other open source projects recommended by the author:

  1. Java Guide: [java learning + interview guide] covers the core knowledge that most Java programmers need to master.
  2. Springboot-guide: Spring Boot tutorial for beginners and experienced developers (in spare time maintenance, welcome to maintain together).
  3. Programmer advancement: I think technicians should have some good habits!
  4. Spring security JWT Guide: get started from scratch! Spring security with JWT (including permission verification) backend part code.

official account

a commonplace talk of an old scholar! How does the database store time? Do you really know?