数据类型

timestamp vs datetime

类型	存储长度	时间范围
timestamp	4字节	'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
datetime	8字节	'1000-01-01 00:00:00' to '9999-12-31 23:59:59'

mysql的timestamp值自动从当前时区转换到utc时区存储，并且自动从utc时区转换为当前系统时区检索返回

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
(This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time.

在mysql5.7之后，datetime字段也可以指定默认值，并且格式和timestamp一样

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
或
DEFAULT 0 or DEFAULT '2000-01-01 00:00:00'

unix timestamp

所有的linux系统文件系统底层存储的都是UTC时间，也就是说都是自1970年0时0分0秒以来的UTC标准时间的秒数。

无论系统配置是什么时区，显示如何不同，底层存储都是一致的。

在shell环境下 >date '+%s' 即可得到

在mysql环境下>select unix_timestamp();即可得到

unix timestamp + mysql timestamp

按照mysql配置的时区，将'2020-02-27 11:48:49'转换成 utc时间，然后再算减去 1970的秒数

select unix_timestamp('2020-02-27 11:48:49')
-> 1582775329

从unix timestamp转换回mysqltimestamp。因为timestamp的输出时，会把utc时间转回当前时区的时间

select from_unixtime(1582775329)
-> '2020-02-27 11:48:49'

对应mysql的时区配置不变，那么用 1的输出做2的输入，永远会显示相同的时间

mysql 时区转换

CONVERT_TZ (datetime, from_tz, to_tz)；

SELECT CONVERT_TZ('2013-07-22 18:41:37','+08:00','+00:00') as UTC;   

+---------------------+
| UTC                 |
+---------------------+
| 2013-07-22 10:41:37 |
+---------------------+
1 row in set (0.00 sec)
————————————————
版权声明：本文为CSDN博主「Stewart」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/stewart/article/details/9412163

index

数据库

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 10.2.8-MariaDB-log |
+--------------------+
1 row in set (0.05 sec)

show create table test_partition;

test_partition | CREATE TABLE `test_partition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cdata` date DEFAULT NULL,
  `gmt_create` timestamp NULL DEFAULT curtime(),
  `gmt_update` datetime DEFAULT curtime(),
  `v` int(11) DEFAULT NULL,
  `gmt_st` bigint(20) DEFAULT unix_timestamp(),
  PRIMARY KEY (`id`),
  KEY `test_partition_gmt_create_index` (`gmt_create`),
  KEY `test_partition_v_index` (`v`),
  KEY `test_partition_gmt_st_index` (`gmt_st`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8

timestamp index

explain select * from test_partition where gmt_create between '2020-02-01' and '2020-03-01'

+------+-------------+----------------+------+---------------------------------+------+---------+------+------+-------------+
| id   | select_type | table          | type | possible_keys                   | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------------+------+---------------------------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | test_partition | ALL  | test_partition_gmt_create_index | NULL | NULL    | NULL |   24 | Using where |
+------+-------------+----------------+------+---------------------------------+------+---------+------+------+-------------+
1 row in set (0.06 sec)

datetime index

explain select * from test_partition where gmt_update between '2020-02-01' and '2020-03-01';

+------+-------------+----------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | test_partition | ALL  | NULL          | NULL | NULL    | NULL |   24 | Using where |
+------+-------------+----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.05 sec)

datetime + int

explain select * from test_partition where gmt_update between '2020-02-01' and '2020-03-01' and v between 100 and 500;

+------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+------------------------------------+
| id   | select_type | table          | type  | possible_keys          | key                    | key_len | ref  | rows | Extra                              |
+------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+------------------------------------+
|    1 | SIMPLE      | test_partition | range | test_partition_v_index | test_partition_v_index | 5       | NULL |    3 | Using index condition; Using where |
+------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+------------------------------------+
1 row in set (0.06 sec)