Mysql中TimeStamp、DateTime、Date关于时区(TimeZone)的问题

最新推荐文章于 2024-04-11 10:33:53 发布
最新推荐文章于 2024-04-11 10:33:53 发布
阅读量2.6w 收藏 29
点赞数 9
分类专栏： 数据库 文章标签： mysql timestamp datetime TimeZone Date
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_38072683/article/details/105011313
版权

存储时间的几种方式

一般情况下：数据库可以这样存储时间：

  • 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 displays DATE 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 displays DATETIME values in YYYY-MM-DD hh:mm:ssformat. 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 or TIMESTAMP 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 a DATETIME or TIMESTAMP 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 for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP 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 as DATETIME.)

存储时，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 the time_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 for TZ and --timezone are system dependent. Consult your operating system documentation to see what values are acceptable.

    要在启动时为MySQL Server明确指定系统时区，请在启动mysqldTZ之前设置环境变量。如果使用mysqld_safe启动服务器，则其 选项提供了另一种设置系统时区的方法。对于允许值和 是取决于系统。请查阅操作系统文档，以了解可以接受的值。 --timezoneTZ--timezone

  • The server current time zone. The global time_zone system variable indicates the time zone the server currently is operating in. The initial time_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 global time_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() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP 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 in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME 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表如图所示：

在这里插入图片描述

gmt_create是timestamp类型的

当前会话时区为+8:00即东八区，查询所有用户：

在这里插入图片描述

当前session时区为'+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

Honyelchak
关注
  • 9
    点赞
  • 踩
  • 29
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL timestamp 与时区问题
m0_71777195的博客
06-26 1909
一番了解下来，最易用的其实还是bigint和datetime这两个时区无关的类型，时区相关的操作直接由我们自己控制最理想。并且也没有timestamp的时间限制。优先使用bigint和datetime，国际化在代码层面做。要使用timestamp，需要了解清楚各种时区的信息并做好配置。防止因为机器时区改变、数据库时区改变而影响查询的结果。
DateTime关于时区的学习
weixin_30779691的博客
03-19 254
一.时区的概念 首先来了解时区的概念。为了解决世界不同各地在时间上的差异，人们定义了时区，时区是地球上的区域使用同一个时间定义。人们将时区分为24个，它们是中时区（零时区）、东1-12区，西1-12区。每个时区横跨经度15度，时间正好是1小时。其中中时区就是格林尼治标准时间。以这个时间为基准，东加西减。我们中国虽然跨越了五个时区，但是统一采用北京时间，也就是东八区。这面有几个...
mysql的timestamp会存在时区问题？，互联网公司面试一般问什么
最新发布
2401_84170522的博客
04-11 785
mysql驱动在发送sql前，会将jdbc中的Date对象参数，根据serverTimeZone配置的时区转化为日期字符串后，再发送sql请求给mysql server，同样在mysql server返回查询结果后，结果中的日期值也是日期字符串，mysql驱动会根据serverTimeZone配置的时区，将日期字符串转化为Date对象。因此，当serverTimeZone与数据库实际时区不一致时，会发生时区转换错误，导致时间偏差，如下：a、比如sql参数是一个Date对象，时间值是东8区的。
MySQL设置时区
Java+GO+JS全栈工程师-鹤冲天的博客：编程辅导~商务合作~技术交流
09-08 1万+
MySQL时区设置对于数据库的处理时间值非常重要，可以让不同的时区用户正确的处理时间值。本文从设置时区的方法、时区转换和时区的影响三个方面详细说明了MySQL的时区设置，在实际开发中需要根据实际需要进行设置和使用。
MySQL time_zone
zsensei的博客
10-03 2万+
使用的是 MySQL 5.7在使用 SSM 框架时，连接数据库出现错误 The server time zone value ‘xxxxx’(这里乱码了) is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the
UTC、TimeZone、TimeStamp
Ivan Song的专栏
03-09 1936
UTC、TimeZone、TimeStamp
Mysql日期类型DATETIME和TIMESTAMP区别
一路的博客
03-18 3870
在日常数据库设计中，几乎每张业务表都带有一个日期列，用于记录每条记录产生和变更的时间。比如用户表会有一个日期列记录用户注册的时间、用户最后登录的时间。又比如，电商行业中的订单表（核心业务表）会有一个订单产生的时间列，当支付时间超过订单产生的时间，这个订单可能会被系统自动取消。 日期类型虽然常见，但在表结构设计中也容易犯错，比如很多开发同学都倾向使用整型存储日期类型，同时也会忽略不同日期类型对于性能可能存在的潜在影响。所以你有必要认真看看这篇文章，举一反三，在自己的业务中做好日期类型的设计。 日期类型 MyS
获取当天零点的时间戳 `now - ( now + 8 * 60 * 60) % daySecond` 的解释
无数_Mirage的博客
10-24 5066
网上关于获取当天零点时间戳，很多都提到了一个效率很高的算法： long now = System.currentTimeMillis(); long daySecond = 24L * 60 * 60 * 1000; // 一天的毫秒值 long dayTime = now - (now + TimeZone.getDefault().getRawOffset()) % daySecond; // TimeZone.getDefault().getRawOffset() 方法用于获取以毫秒为单位的时间量添加
Mysql中的Datetime和Timestamp比较
09-10
MySQL数据库系统中，时间数据类型是用来存储日期和时间值的，其中包括`DATE`、`DATETIME`和`TIMESTAMP`。这些类型在某些方面相似，但在其他方面却有着明显的区别。下面将详细介绍`DATETIME`和`TIMESTAMP`的比较，并...
MySQL中datetime和timestamp的区别及使用详解
09-08
在MySQL数据库中，datetime和timestamp是两种常用的日期和时间数据类型，它们虽然都可以用来存储日期和时间信息，但在使用和处理上存在一些显著的区别。本文将深入探讨这两种数据类型的差异以及如何在实际应用中选择...
关于Java中的mysql时区问题详解
08-19
"Java 中的 MySQL 时区问题详解" Java 中的 MySQL 时区问题是一种常见的问题，尤其是在分布式系统中，对时区的处理尤为重要。本文将详细介绍 Java 中的 MySQL 时区问题，并通过示例代码进行讲解。 一、时区问题的...
mysql datetime 时区_MySQL性能调优（一）
weixin_39676972的博客
11-28 277
性能监控发现sql运行慢的时候，得知道具体哪里执行时间长。那得进行性能监控show profile查询剖析工具，可以哪个步骤执行时间较长可以看到select 的这条语句实际的执行时间是 0.00013475然后 show profile; 可以查看具体的运行时间。这个SHOW PROFILE和SHOW PROFILES语句显示指示在当前会话期间执行的语句的资源使用情况的分析信息。话不多说，先来连接...
c#：细说时区、DateTime和DateTimeOffset在国际化中的应用
火焰
06-29 3487
先说下结论： 如果系统不考虑全球化的话，那么我们不用考虑时区的问题，因为我们可以认为中国境内的计算机全部用的是北京时间。 1. 时区的来源和划分 地球自转一圈是360度，共24小时，所以1小时15度，即：15度就是1个时区。 国际定义了本初子午线（0度经线）的位置在英国伦敦格林尼治天文台，自然世界时（UTC）也是指的格林尼治天文台（GMT）那里的时间。 认为北京处在东经120度线上，也就是东八区上，所以北京相比UTC时间有8个小时的时差。 由于地球自西向东转，所以北京比格林尼治最先接触阳光，自然就认为北京的
mysql timestamp类型_MySQL timestamp的类型与时区实例详解
weixin_29011239的博客
01-25 580
MySQL timestamp的类型与时区MySQL的timestamp类型时间范围between '1970-01-01 00:00:01' and '2038-01-19 03:14:07'，超出这个范围则值记录为'0000-00-00 00:00:00'，该类型的一个重要特点就是保存的时间与时区密切相关，上述所说的时间范围是UTC(Universal Time Coordinated)标准，...
mysql 日期和时间函数_MySQL常用日期时间函数
weixin_35922953的博客
01-27 206
日期和时间函数可能的需求：当前时间是多少、下个月的今天是星期几、统计截止到当前日期前 3 天的收入总和……上述需求就需要使用日期和时间函数来实现：MySQL服务器中的三种时区设置：①系统时区---保存在系统变量system_time_zone②服务器时区---保存在全局系统变量global.time_zone③每个客户端连接的时区---保存在会话变量session.time_zone注意：客户端时...
mysql 时区设置
ZQB765720343的专栏
04-25 3万+
方法一：通过mysql命令行模式下动态修改1.1 查看mysql当前时间，当前时区> select curtime();  #或select now()也可以+-----------+| curtime() |+-----------+| 15:18:10 |+-----------+> show variables like "%time_zone%";+--------------...
mysql datetime类型处理计算时间差
ljwy1234的博客
04-13 2万+
数据库  与上面有所差距 下面的查询没带时间查询  想要满足上面一段时间查询 只需要带上where 就好第一个sql select id,userid,count(*) as loginnum,sum(TIMESTAMPDIFF(MINUTE,logintime, logouttime)) as times from log group by userid;第2个sql 第二个没看懂 也不知道做的...
mysql时区问题
Nero_G的博客
05-03 466
客户端连接时时区是根据当前连接session时区设置，并且只会影响函数和timestamp存取；时区设置不影响UTC_TIMESTAMP()函数和DATE、TIME、DATETIME数据列。 Per-connection time zones. Each client that connects has its own time zone setting, given by the ses...
为什么Django设置时区为TIME_ZONE = 'Asia/Shanghai' USE_TZ = True后，存入mysql中的时间只能是UTC时间
热门推荐
qq_27361945的博客
06-05 3万+
这个问题的解释还得看Django官方文档。其实是我理解错了，因为Mysql存储的时间不能灵活设置时区，不像datetime对象有一项参数专门指定时区，所以为了统一全球的时间，必须使用国际标准时间UTC，否则就会乱套。所有时间在存如数据库前，必须转换成UTC时间。比如北京时间8点，存入mysql变成0点（UTC）。一般不跨时区的应用，可以不使用时区，即在settings.py设置USE_TZ = F...
在MySQL中，时间类型DATETIME、DATE、TIME、TIMESTAMP转化到Java中
06-07
在MySQL中，时间类型有四种，分别是DATETIME、DATE、TIME、TIMESTAMP，这些类型在Java中的转换方式如下： 1. DATETIME类型 在Java中，可以使用java.time.LocalDateTime类来表示DATETIME类型。在通过JDBC从MySQL中读取DATETIME类型的值时，可以使用ResultSet类的getTimestamp方法获取java.sql.Timestamp类型的值，然后再将其转换为java.time.LocalDateTime类型： ```java ResultSet rs = stmt.executeQuery("SELECT datetime_column FROM table"); while (rs.next()) { Timestamp timestamp = rs.getTimestamp("datetime_column"); LocalDateTime datetime = timestamp.toLocalDateTime(); // ... } ``` 在将java.time.LocalDateTime类型的值写入到MySQL的DATETIME类型的字段时，可以使用PreparedStatement类的setTimestamp方法： ```java PreparedStatement pstmt = conn.prepareStatement("INSERT INTO table (datetime_column) VALUES (?)"); LocalDateTime datetime = LocalDateTime.now(); Timestamp timestamp = Timestamp.valueOf(datetime); pstmt.setTimestamp(1, timestamp); pstmt.executeUpdate(); ``` 2. DATE类型 在Java中，可以使用java.time.LocalDate类来表示DATE类型。在通过JDBC从MySQL中读取DATE类型的值时，可以使用ResultSet类的getDate方法获取java.sql.Date类型的值，然后再将其转换为java.time.LocalDate类型： ```java ResultSet rs = stmt.executeQuery("SELECT date_column FROM table"); while (rs.next()) { Date date = rs.getDate("date_column"); LocalDate localDate = date.toLocalDate(); // ... } ``` 在将java.time.LocalDate类型的值写入到MySQL的DATE类型的字段时，可以使用PreparedStatement类的setDate方法： ```java PreparedStatement pstmt = conn.prepareStatement("INSERT INTO table (date_column) VALUES (?)"); LocalDate localDate = LocalDate.now(); Date date = Date.valueOf(localDate); pstmt.setDate(1, date); pstmt.executeUpdate(); ``` 3. TIME类型 在Java中，可以使用java.time.LocalTime类来表示TIME类型。在通过JDBC从MySQL中读取TIME类型的值时，可以使用ResultSet类的getTime方法获取java.sql.Time类型的值，然后再将其转换为java.time.LocalTime类型： ```java ResultSet rs = stmt.executeQuery("SELECT time_column FROM table"); while (rs.next()) { Time time = rs.getTime("time_column"); LocalTime localTime = time.toLocalTime(); // ... } ``` 在将java.time.LocalTime类型的值写入到MySQL的TIME类型的字段时，可以使用PreparedStatement类的setTime方法： ```java PreparedStatement pstmt = conn.prepareStatement("INSERT INTO table (time_column) VALUES (?)"); LocalTime localTime = LocalTime.now(); Time time = Time.valueOf(localTime); pstmt.setTime(1, time); pstmt.executeUpdate(); ``` 4. TIMESTAMP类型 在Java中，可以使用java.time.LocalDateTime类来表示TIMESTAMP类型。在通过JDBC从MySQL中读取TIMESTAMP类型的值时，可以使用ResultSet类的getTimestamp方法获取java.sql.Timestamp类型的值，然后再将其转换为java.time.LocalDateTime类型： ```java ResultSet rs = stmt.executeQuery("SELECT timestamp_column FROM table"); while (rs.next()) { Timestamp timestamp = rs.getTimestamp("timestamp_column"); LocalDateTime datetime = timestamp.toLocalDateTime(); // ... } ``` 在将java.time.LocalDateTime类型的值写入到MySQL的TIMESTAMP类型的字段时，可以使用PreparedStatement类的setTimestamp方法： ```java PreparedStatement pstmt = conn.prepareStatement("INSERT INTO table (timestamp_column) VALUES (?)"); LocalDateTime datetime = LocalDateTime.now(); Timestamp timestamp = Timestamp.valueOf(datetime); pstmt.setTimestamp(1, timestamp); pstmt.executeUpdate(); ```

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Honyelchak

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值