Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to achieve storage time in MySQL database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

1. Remember not to use strings to store dates

Strings take up more space!

Date stored in strings is inefficient (compared character by character) and cannot be calculated and compared with date-related API.

The choice between 2.Datetime and Timestamp

Datetime and Timestamp are two data types with similar retention times provided by MySQL.

How on earth should they choose between them?

Usually we will choose Timestamp first.

2.1 the DateTime type does not have time zone information

The DateTime type has no time zone information (time zone independent)

When your time zone changes, such as changing your server address or client connection time zone settings, it will cause you to read the wrong time from the database.

Timestamp is related to the time zone.

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

Let's demonstrate it in practice!

Create a 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 the data:

Select dsqlate_time,time_stamp from time_zone_test

Results:

+-+ +

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

1. View the current session time zone

SELECT @ @ session.time_zone

two。 Set the current session time zone

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

3. Database global time zone settings

SELECT @ @ global.time_zone

4. Set the global time zone

SET GLOBAL time_zone ='+ 8 Europe/Helsinki';2.2 DateTime; SET GLOBAL time_zone = 'type consumes more space

Timestamp only needs 4 bytes of storage, but DateTime consumes 8 bytes of storage. However, this also creates a problem, because 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

Timestamp varies slightly from version to version of MySQL.

3. Then look at the MySQL date type storage space.

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

You can see that MySQL after 5.6.4 has an extra decimal place that requires 0-3 bytes. Datatime and Timestamp will have several different storage footprints.

For convenience, this article defaults that Timestamp only uses 4 bytes of storage, but DateTime consumes 8 bytes of storage.

4. Is a numeric timestamp a better choice?

In many cases, we also use int or bigint values, that is, timestamps, to represent time.

This kind of storage method has some advantages of Timestamp type, and it is more efficient to use it for date sorting and comparison, and it is also very convenient across systems. after all, it is only a stored value. The disadvantage is also obvious that the readability of the data is so poor that you can't see the specific time intuitively.

The timestamp is defined as follows:

The timestamp is defined as starting from a base time, which is "1970-1-1 00:00:00 + 0:00", from which time is expressed in integers and in seconds, which increases as time goes by. In this way, I only need a number to represent the time perfectly, and this value is an absolute value, that is, no matter where we are on the earth, the timestamp of the time is the same, the generated values are the same, and there is no concept of time zone, so there is no need for additional conversion in the time transmission in the system, only when it is displayed to the user. The local time that is converted to string format.

The actual operation in the database:

Mysql > select UNIX_TIMESTAMP ('2020-01-11 09 purl 53 purl 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) | +-- + | 0-01-11 09:53:32 | +-- + 1 row in set (1578707612 sec) 5. Summary

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

Each way has its own advantages, according to the actual scene is the king. Here is a simple comparison of these three methods, so that you can choose the correct data type for storage time in your actual development:

6. Set the default value of mysql date type field to support 0000 format [mysqld] sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION this article on "how to achieve storage time in MySQL database" ends here. I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.