mysql date/datetime/timestamp and timezone

获取Server时区

$ SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone
@@global.time_zone: SYSTEM
@@session.time_zone: SYSTEM
@@system_time_zone: +08

获取和设置Connection时区

set time_zone="+00:00"
select now()

Datetime类型本质上是字符串存储的

由于Datetime是以字符串存储的，其值在写入时已经确定，只支持2020-02-02 20:20:20的写入。
所以，改变connection或者server的时区，不会更新已有的数据。

此外，如果对datetime作匹配，不会应用时区信息：

$ SELECT id,create_time FROM test_tab WHERE create_time="2023-03-13 11:51:00" \G'
*************************** 1. row ***************************
         id: 160482
create_time: 2023-03-13 11:51:00


$ SELECT id,create_time FROM test_tab WHERE create_time="2023-03-13 11:51:00+08:00" \G'
*************************** 1. row ***************************
         id: 160482
create_time: 2023-03-13 11:51:00

这说明Datetime和普通的字符串匹配不一样，mysql会把字符串先转成时区再进行操作

改变时区，并不会改变已有的数据。
我们可以将mysql对于Datetime类型的操作，可以理解为以下步骤：

• 将时间标准化，移除多余的字符串

  • 2022-02-02 20:20:20XXXXX 多余的XXXX被移除掉，比如 2022-02-02 20:20:20z，看起来是0时区，但实际上最终和2022-02-02 20:20:20等价
  • 但是mysql8支持 +08:00的时区后缀： https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html

  Beginning with MySQL 8.0.19, you can specify a time zone offset when inserting TIMESTAMP and DATETIME values into a table. The offset is appended to the time part of a datetime literal, with no intravening spaces, and uses the same format used for setting the time_zone system variable, with the following exceptions:

• 再对时间进行存储或比较

Date相关的函数

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_adddate

UNIX_TIMESTAMP(DATE)会受时区影响

$ SET time_zone="+00:00"
$ SELECT unix_timestamp("2023-03-13 11:51:00")\G
1678708260

$ SET time_zone="+08:00"
$ SELECT unix_timestamp("2023-03-13 11:51:00")\G
1678679460

1678679460s - 1678708260s = 28800s = 8h

对于不合法的日期，UNIX_TIMESTAMP返回0

$ select unix_timestamp("2")
0.000000
$ select unix_timestamp("2022")
0.000000
$ select unix_timestamp("2022-02-??")
0.000000
$ select unix_timestamp("2022-02-02")
1643731200

#  不合法的日期，比如12-32日，也返回0
$ select unix_timestamp("2022-12-32")
0

对于1970-01-01以前的日期,UNIX_TIMESTAMP返回0

$ SELECT unix_timestamp("1900-01-01")
0
$ SELECT unix_timestamp("1970-01-01")
0
$ SELECT unix_timestamp("1970-01-02")
59400

注意，nodejs中的getTime会返回负数的时间戳

> new Date("1970-01-01").getTime()
0
> new Date("1900-01-01").getTime()
-2208988800000

DATEDIFF对于1970-01-01以前的日期仍然有效

$ SELECT DATEDIFF("1901-01-01 23:59:59","1900-12-31 23:59:59")
1
$ SELECT DATEDIFF("1901-01-01 23:59:59","1900-12-32 23:59:59")
NULL

字面量(mysql5.7也支持)

SELECT TIME'20:20:20'
SELECT DATE'2020-02-02'
SELECT TIMESTAMP'2020-02-02 20:20:20'

有效的时间范围

DATE： 1000-01-01 to 9999-12-31
DATETIME： 1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP: 1970-01-01 00:00:01 to 2038-01-19 03:14:07

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in ‘YYYY-MM-DD’ format. The supported range is ‘1000-01-01’ to ‘9999-12-31’.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD hh:mm:ss’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

自动转换

https://dev.mysql.com/doc/refman/8.0/en/datetime.html
TIMESTAMP类型自动转换为UTC时间存储，查询时再转换为当前时区。
但是对于DATETIME类型没有这个转换，仅仅是以标准化后的字符串形式存储。

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.