Mysql中TimeStamp、DateTime、Date关于时区(TimeZone)的问题
存储时间的几种方式
一般情况下:数据库可以这样存储时间:
- bigint(存毫秒数Long)
- 存储时间戳,然后
new Date(long date)
转化为时间
- 存储时间戳,然后
- DateTime
- TimeStamp时间戳
阅读官方文档
1、DATE, DATETIME, TIMESTAMP三者的区别:
首先说一下三者的不同之处:
The
DATE
type is used for values with a date part but no time part. MySQL retrieves and displaysDATE
values in'*
YYYY-MM-DD*'
format. The supported range is'1000-01-01'
to'9999-12-31'
.Date只表示日期,年-月-日,不表示具体的时间
The
DATETIME
type is used for values that contain both date and time parts. MySQL retrieves and displaysDATETIME
values inYYYY-MM-DD hh:mm:ss
format. The supported range is'1000-01-01 00:00:00'
to'9999-12-31 23:59:59'
.DateTime即表示日期,又表示时间,YYYY-MM-DD hh:mm:ss,它更像是一个·字符串表示
The
TIMESTAMP
data type is used for values that contain both date and time parts.TIMESTAMP
has a range of'1970-01-01 00:00:01'
UTC to'2038-01-19 03:14:07'
UTC.
TIMESTAMP
和DateTime类似,即表示时间,又表示日期,A
DATETIME
orTIMESTAMP
value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into aDATETIME
orTIMESTAMP
column is stored rather than discarded. With the fractional part included, the format for these values is'*
YYYY-MM-DD hh:mm:ss*[.*
fraction*]'
, the range forDATETIME
values is'1000-01-01 00:00:00.000000'
to'9999-12-31 23:59:59.999999'
, and the range forTIMESTAMP
values is'1970-01-01 00:00:01.000000'
to'2038-01-19 03:14:07.999999'
. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 11.2.6, “Fractional Seconds in Time Values”.DATETIME或TIMESTAMP值可以包含最高达微秒(6位)精度的尾随小数秒部分。特别是,插入DATETIME或TIMESTAMP列的值中的任何小数部分都将被存储而不是丢弃。包含小数部分时,这些值的格式为“YYYY-MM-DD hh:MM:ss[.fraction]”,
DATETIME值的范围为“1000-01-01 00:00:00.000000”到“9999-12-31 23:59:59.999999”,TIMESTAMP值的范围为“1970-01-01 00:00:01.000000”到“2038-01-19 03:14:07.999999”。
小数部分应始终与其余时间用小数点分隔;不能识别其他小数秒分隔符
2、TIMESTAMP
与TimeZone的关系
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 asDATETIME
.)存储时,MySQL将
TIMESTAMP
值从当前时区转换为UTC时间进行存储,查询时,将数据从UTC转换为检索的当前时区。(其他类型(如DATETIME)不会发生这种情况。)By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store.
默认情况下,每个连接的当前时区是服务器的时间。时区可以根据每个连接进行设置。只要时区设置保持不变,就可以得到存储的相同值。
If you store a
TIMESTAMP
value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of thetime_zone
system variable. For more information, see Section 5.1.13, “MySQL Server Time Zone Support”.如果存储
TIMESTAMP
值,然后更改时区并检索该值,则检索到的值与存储的值不同。发生这种情况的原因是没有使用同一时区在两个方向上进行转换。当前时区可用作时区系统变量的值。有关详细信息,请参阅第5.1.13节“MySQL服务器时区支持”。
3、关于Mysql中TimeZone的简介:
MySQL Server maintains several time zone settings:
MySQL Server维护几个时区设置:
The system time zone. When the server starts, it attempts to determine the time zone of the host machine automatically and uses it to set the
system_time_zone
system variable. The value does not change thereafter.系统时区。服务器启动时,它将尝试自动确定主机的时区,并使用它来设置
system_time_zone
系统变量。此后该值不变。To explicitly specify the system time zone for MySQL Server at startup, set the
TZ
environment variable before you start mysqld. If you start the server using mysqld_safe, its--timezone
option provides another way to set the system time zone. The permissible values forTZ
and--timezone
are system dependent. Consult your operating system documentation to see what values are acceptable.要在启动时为MySQL Server明确指定系统时区,请在启动mysqld
TZ
之前设置环境变量。如果使用mysqld_safe启动服务器,则其 选项提供了另一种设置系统时区的方法。对于允许值和 是取决于系统。请查阅操作系统文档,以了解可以接受的值。--timezone
TZ
--timezone
The server current time zone. The global
time_zone
system variable indicates the time zone the server currently is operating in. The initialtime_zone
value is'SYSTEM'
, which indicates that the server time zone is the same as the system time zone.服务器当前时区。全局
time_zone
系统变量指示服务器当前正在运行的时区。初始time_zone
值为'SYSTEM'
,指示服务器时区与系统时区相同。Per-session time zones. Each client that connects has its own session time zone setting, given by the session
time_zone
variable. Initially, the session variable takes its value from the globaltime_zone
variable, but the client can change its own time zone with this statement:会话时区。每个连接的客户端都有自己的会话时区设置,由会话
time_zone
变量指定。最初,会话变量从全局变量获取其值time_zone
,但是客户端可以使用以下语句更改其自己的时区:
关键就是会话TimeZone,每个客户端
数据库默认的时区都是System
。
4、如何修改timezone的值
timezone
值可以以几种格式给出,都不区分大小写:
- 作为值
'SYSTEM'
,指示服务器时区与系统时区相同。- 作为表示一个字符串的从表单的UTC偏移 ,带有前缀
+
或-
,例如,+10:00
,'-6:00'
, or+05:30
,例如我们是东八区+8:00
- MySQL 8.0.19之前,该值必须在范围
'-12:59'
到'+13:00'
,包容性;- MySQL 8.0.19之后,允许范围为
'-14:00'
到'+14:00'
,包容性。- 作为命名的时区,例如
'Europe/Helsinki'
,'US/Eastern'
或'MET'
。仅当mysql
已经创建并填充了数据库中的时区信息表时,才能使用命名时区 。
-- 设置session时区
SET time_zone = 'Asia/Shanghai';
-- 查询系统时区和session时区
SELECT @@global.time_zone, @@session.time_zone;
-- 当前session时区
show VARIABLES like '%time_zone%';
5、TimeZone的影响
The session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as
NOW()
orCURTIME()
, and values stored in and retrieved fromTIMESTAMP
columns. Values forTIMESTAMP
columns are converted from the session time zone to UTC for storage, and from UTC to the session time zone for retrieval.会话时区设置会影响对时区敏感的时间值的显示和存储。这包括由诸如
NOW()
或 的函数显示的值CURTIME()
,以及存储在TIMESTAMP
列中并从列中检索的值。TIMESTAMP
列的值从会话时区转换为UTC以进行存储,并从UTC转换为会话时区以进行检索。The session time zone setting does not affect values displayed by functions such as
UTC_TIMESTAMP()
or values inDATE
,TIME
, orDATETIME
columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting fromTIMESTAMP
values. If you want locale-specific arithmetic forDATE
,TIME
, orDATETIME
values, convert them to UTC, perform the arithmetic, and then convert back.会话时区设置不会影响
UTC_TIMESTAMP()函数
、DATE
、TIME
,或DATETIME
列中的或值 。这些数据类型中的值也不会像TimeStamp转换为UTC存储;时区仅在从TIMESTAMP
值转换时适用 。如果要针对DATE
,TIME
或DATETIME
值进行语言环境特定的算术 ,请将其转换为UTC,执行该算术,然后再转换回去。
demo测试
可能上边的大段文字,你没有读懂,那我现在给你举一个例子:
有一个user
表如图所示:
当前会话时区为+8:00
即东八区,查询所有用户:
用set time_zone=‘+0:00’修改时区
,再次查询发现TimeStamp
类型的gmt_create
发生了变化。
总结
-
Date
、Time
、DateTime
类型不支持时区转换。 -
TimeStamp
列的值从会话时区转换为UTC以进行存储,并从UTC转换为会话时区以进行检索。- 可以理解为它存储时区,而
DateTime
不存储时区。 - 且从
DateTime
和TimeStamp
的表示范围上,我们就能看出来DateTime
有点字符串的意思,而TimeStamp
则不是。
- 可以理解为它存储时区,而
参考资料
https://developer.aliyun.com/article/728315
https://segmentfault.com/a/1190000016426048
https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
上一篇: docker搭建redis集群
下一篇: 给你藏宝图哟
推荐阅读
-
MySQL数据库中的Date,DateTime和TimeStamp类型详解_MySQL
-
MySQL中DATETIME、DATE和TIMESTAMP类型的区别
-
MySQL数据库中的Date,DateTime和TimeStamp类型_MySQL
-
MySQL数据库中的Date,DateTime和TimeStamp类型_MySQL
-
详解MySQL中timestamp和datetime时区问题导致做DTS遇到的坑
-
MySQL数据库中的Date,DateTime和TimeStamp类型
-
MySQL数据库中的Date,DateTime和TimeStamp类型详解_MySQL
-
mysql中DATETIME、DATE和TIMESTAMP的区别_MySQL
-
MySQL数据库中的Date,DateTime和TimeStamp类型
-
Mysql中TimeStamp、DateTime、Date关于时区(TimeZone)的问题