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

This article will explain in detail what the time types in mysql are, and the editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Selection of time type

The time field type can be datetime or timestamp. The following table shows the difference between the two:

Timestamp is translated into Chinese as "timestamp", which is the number of seconds from the current time to the first year of Unix (00:00:00, January 1, 1970), occupies 4 bytes, and is saved in UTC format. It automatically retrieves the current time zone and converts it. Datetime is stored in 8 bytes and does not retrieve the time zone. In other words, for timestamp, if the storage time zone is different from the retrieval time zone, then the data will be different. For datetime, what you save is what you get. Here are some common cases and suggestions for selection.

It is recommended to select datetime according to the storage range, such as production time, shelf life and so on, because datetime can store a wider range.

It is recommended to use timestamp to record the insertion time and modification time of the data in this line.

Timestamp is selected for the time field related to the time zone.

If you just want to represent the year, date, and time, you can also use year, date, and time, which occupy 1, 3, and 3 bytes, respectively, and datetime is their collection.

If the timestamp field is often used for queries, we can also use the built-in functions FROM_UNIXTIME () and UNIX_TIMESTAMP () of MySQL to convert date and timestamp numbers back and forth. After conversion, we can use INT UNSIGNED to store time. The numbers are continuous, take up less space, and indexes can be used to improve query performance. The following is a demonstration table-building statement and timestamp-related transformation SQL:

CREATE TABLE `timetime` (`increment_ id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key', `col1`time` 2020-10-01 00increment_ COMMENT 'expiration', `unix_ createtime` int unsigned NOT NULL COMMENT 'creation timestamp', `create_ time`timetime` creation time', `update_ time`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time', PRIMARY KEY (`timeid`) KEY `idx_unix_ createtime` (`unix_ createtime`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='time test table' # insert data insert into tb_time (unix_createtime,create_time) values (UNIX_TIMESTAMP (now ()), now ()) # timestamp conversion between numbers and time select UNIX_TIMESTAMP ('2020-05-06 00 select UNIX_TIMESTAMP (' 05-05-06 00 mysql) select FROM_UNIXTIME (1588694400) this is the end of the article on "what are the types of time in time?" I hope the above content can be helpful 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.