mysql时间

背景

前几天朋友发了一个问题

-- 表结构 type/status/create_time
-- type区分度比status高，应该如何加索引

1. type
2. type status create_time
3. type create_time

几个人讨论后有个人说不需要create_time，说create_time只有在 = 才生效
但按我自己的理解是create_time也可以走索引范围查询
因此搜了一些资料加深自己的理解

日期索引

先说结论，日期索引是可以走范围查询的
如下展示了mysql中单索引和联合索引的使用

我主要是纠结联合索引的画法
后面参考了CSDN的一篇文章

而取舍就是是否需要再加一个status
即(type status create_time) 和 (type create_time)
由于status区分度不高，实际上可以不加
加了就是耗费点空间，按我目前的知识是这么理解的

几种时间

类型	格式	范围
Date	YYYY-MM-DD	‘1000-01-01’ to ‘9999-12-31’
DATETIME	YYYY-MM-DD hh:mm:ss.000000	‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
TIMESTAMP	YYYY-MM-DD hh:mm:ss.000000	‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC

timestamp

timestamp是携带时区的，存储时以UTC存储
mysql存储timestamp时会转换为UTC存储，取出来的时候会恢复成当前服务器的TimeZone
默认下time zone是服务器的时间
JDBC中，我们可以给每个连接设置time zone
如果你存进数据库后，修改了连接的serverTimeZone，那么取出来的值也会不一样
比如在GMT+8时，取出来的值是2022-7-8 08:57:35
那么GMT+6时取出来的值是2022-7-8 06:57:35

注意DATETIME不携带时区

timestamp最大支持2038年是因为Int最大2147483647

其他

myql 8.0.19后可以给TIMESTAMP or DATETIME在表中指定time zone offset
非法的DATE, DATETIME, or TIMESTAMP会被转化为0，如果SQL mode 支持NO_ZERO_DATE的话
mysql 8.0.22后可以通过CAST和at time zone将timestamp转换成datetime

-- mysql会自动识别一些格式的时间
-- 等价于create_time > '2022-07-01'
SELECT * FROM demo WHERE create_time > '22:07:01';
-- 等价于create_time > '0000-00-00'，因为50不是一个合法的月份
SELECT * FROM demo WHERE create_time > '22:50:01';
-- create_time为timestamp类型，转换为DATETIME
SELECT *, CAST(create_time AT TIME ZONE INTERVAL '+00:00' AS DATETIME) ut FROM time_demo

原文链接：https://blog.csdn.net/qq_29656325/article/details/125892268