What are the time types in mysql

Selection of time type

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

timestamp Translated into Chinese, it is "timestamp". It is the number of seconds from the current time to the first year of Unix (January 1, 1970, 0:00:00). It occupies 4 bytes and is stored in UTC format. It The current time zone is automatically retrieved and converted. Datetime is stored in 8 bytes and no time zone retrieval is performed. In other words, for timestamp, if the time zone when storing is different from the time zone when retrieving, the data taken out will also be different. For datetime, you get what you save. Here are some common cases and selection suggestions.

• Select according to the storage range, such as production time, shelf life, etc. It is recommended to select datetime, because datetime can store a wider range.

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

• Select timestamp 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. They occupy 1, 3, and 3 bytes respectively, and datetime is their set.

If the timestamp field is often used in queries, we can also use MySQL's built-in functions FROM_UNIXTIME(), UNIX_TIMESTAMP() to convert the date Convert back and forth with timestamp numbers. After conversion, you can use INT UNSIGNED to store the time. The numbers are continuous, occupy less space, and indexes can be used to improve query performance. The following is a sample table creation statement and timestamp-related conversion SQL:

CREATE TABLE `tb_time` (
  `increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `col1` datetime NOT NULL DEFAULT '2020-10-01 00:00:00' COMMENT '到期时间',
    `unix_createtime` int unsigned NOT NULL COMMENT '创建时间戳',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`increment_id`),
  KEY `idx_unix_createtime` (`unix_createtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='time测试表';
# 插入数据
insert into tb_time (unix_createtime,create_time) values 
(UNIX_TIMESTAMP(now()),now());
# 时间戳数字与时间相互转换
select UNIX_TIMESTAMP('2020-05-06 00:00:00')
select FROM_UNIXTIME(1588694400)

The above is the detailed content of What are the time types in mysql. For more information, please follow other related articles on the PHP Chinese website!