精华内容
下载资源
问答
  • 关于mysql时区(1) 背景 由于mysql5.7用的人还是比较多,本文还是基于mysql 5.7(不过跟8.x版本应该是差不多的) 本问研究mysql的 常用的两个日期类型和时区的关系 datetime timestamp 在阿里的Java开发手册...

    关于mysql的时区

    背景

    由于mysql5.7用的人还是比较多,本文还是基于mysql 5.7(不过跟8.x版本应该是差不多的)

    本问研究mysql的

    • 常用的两个日期类型和时区的关系

      • datetime
      • timestamp

      在阿里的Java开发手册中,推荐的是用datetime,没说明原因,估计是忌惮timestamp的最大值只能到2038年。

    • 顺便回顾了怎么设置mysql的时区、CST的bug

    一、mysql 的 日期类型和时区的关系

    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.

    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.

    我们常用的关于日期/时间的类型有

    • datetime(也包括date、time)
    • timestamp

    1、总结

    1)timestamp

    按官方的说法,timestamp存储的是UTC时间戳,展示的时候会将时间戳转为当前时区进行展示。

    例如有个字段是在东八区的时候插入的,东八区时候select出来是 2021-04-04 21:51:43,如果数据库的时区变成东九区,比如设置会话时区为东九区set time_zone='+9:00',则select查出的值是2021-04-04 22:51:43

    要不是因为timestamp的范围过小,最大值只能到2038年,我觉得timestamp是最好的最方便的类型

    总结:反正只要知道timestamp存的是时间戳就可以了。

    2)datetime、date、time

    这些类型的字段,官方也说明白了,就是不会帮你转换。说白了就跟写死的字符串一样,存进去是什么样的值,不管你的时区调成什么样都不会再改变了。

    总结:这些类型存的是死值,跟死的字符串一样。

    3)now()、curtime()、curdate()

    这些函数的值,是会随着时区的不同而获取到不同的值的。

    2、动手实验

    用命令行(或者Navicat也行)连上mysql,我的数据库的时区如下

    mysql> show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | CST    |
    | time_zone        | +08:00 |
    +------------------+--------+
    2 rows in set (0.00 sec)
    

    创建库、选中库、创建表

    mysql> create database t_test_tz;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> use t_test_tz
    Database changed
    mysql> create table t_test(id int primary key auto_increment,datetime datetime,timestamp timestamp,str varchar(255));
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> desc t_test;
    +-----------+--------------+------+-----+-------------------+-----------------------------+
    | Field     | Type         | Null | Key | Default           | Extra                       |
    +-----------+--------------+------+-----+-------------------+-----------------------------+
    | id        | int(11)      | NO   | PRI | NULL              | auto_increment              |
    | datetime  | datetime     | YES  |     | NULL              |                             |
    | timestamp | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    | str       | varchar(255) | YES  |     | NULL              |                             |
    +-----------+--------------+------+-----+-------------------+-----------------------------+
    4 rows in set (0.01 sec)
    
    mysql> 
    

    往库里加数据

    mysql> insert into t_test(datetime,timestamp,str) values(now(),now(),'2021-04-05 17:25:42 东八区,字符串类型,方便切换时区时比较该值和另外两个字段');                      
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from t_test;
    +----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
    | id | datetime            | timestamp           | str                                                                                                      |
    +----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
    |  1 | 2021-04-05 17:25:42 | 2021-04-05 17:25:42 | 2021-04-05 17:25:42 东八区,字符串类型,方便切换时区时比较该值和另外两个字段                             |
    +----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>
    

    说明:str这个字段仅仅是用来记录,因为是字符串,这样无论怎么切换时区,这个字段的值都不会变化,方便后续调整数据库时区的时候和datetime和timestamp字段进行比较。

    设置会话的时区为东九区,可以看到会话的时区已经变成东九区(全局时区依然是东八区,这个没有关系)

    mysql> select @@GLOBAL.time_zone,@@SESSION.time_zone;
    +--------------------+---------------------+
    | @@GLOBAL.time_zone | @@SESSION.time_zone |
    +--------------------+---------------------+
    | +08:00             | +08:00              |
    +--------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql> set time_zone='+9:00';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@GLOBAL.time_zone,@@SESSION.time_zone;
    +--------------------+---------------------+
    | @@GLOBAL.time_zone | @@SESSION.time_zone |
    +--------------------+---------------------+
    | +08:00             | +09:00              |
    +--------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql>
    

    再次查看数据

    mysql> select * from t_test;
    +----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
    | id | datetime            | timestamp           | str                                                                                                      |
    +----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
    |  1 | 2021-04-05 17:25:42 | 2021-04-05 18:25:42 | 2021-04-05 17:25:42 东八区,字符串类型,方便切换时区时比较该值和另外两个字段                             |
    +----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> 
    

    对比str,我们插入时的时间是东八区17点,但是看到timestamp的值已经变成了18点,快了一个小时,而datetime的值没有变化。

    这就证明了官网说的,datetime存的是死值,不会随着时区的切换而变化,而timestamp存的是UTC时间戳,当时区调整后,也会输出跟时区相符合的字符串值。

    而对于now() 等等函数,在东八区和东九区,输出的时区是不一样的,下面这几行命令的执行绝对不超过1小时,而看得到 select now() ` 的值相差1小时,这就是说now()这个函数也是会根据时区输出跟时区相匹配的字符串

    mysql> select @@GLOBAL.time_zone,@@SESSION.time_zone;
    +--------------------+---------------------+
    | @@GLOBAL.time_zone | @@SESSION.time_zone |
    +--------------------+---------------------+
    | +08:00             | +08:00              |
    +--------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2021-04-05 17:33:23 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> set time_zone='+9:00';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@GLOBAL.time_zone,@@SESSION.time_zone;
    +--------------------+---------------------+
    | @@GLOBAL.time_zone | @@SESSION.time_zone |
    +--------------------+---------------------+
    | +08:00             | +09:00              |
    +--------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2021-04-05 18:33:43 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> 
    

    上述的查询结果是通过命令行输出的结果,如果通过Java程序输出,就更加复杂了,比如通过springboot+mybatis查询上述 t_test 表,查出的Date字段的值会做什么样的转换,这个有点复杂,下次有空再研究。

    初步来说有这么几个

    • 数据库可以设置时区,运行springboot+mybatis的服务器(我管它叫web服务器)也有一个时区,这2个时区如果不一致,似乎对查询结果会有差异
      • 只调整数据库的时区,查询结果会变吗?
      • 只调整web服务器的时区呢?
    • springboot+mybatis,底层也是jdbc,jdbc的驱动连接mysql会需要一个时区,这个时区是读取数据库的。读出来的值,会不会根据web服务器的时区再转换一次?

    上述问题有点复杂,待有空再研究。

    展开全文
  • MySQL DATETIME 与 TIMESTAMP 类型切换时区数据修复建议1 概述2 创建演示表3 设置时区为 UTC4 插入测试数据5 查询结果6 修改时区 UTC+87 查询数据变化8 插入测试数据9 数据修复10 小结 1 概述 通过实例展示 DATETIME...

    1 概述

    通过实例展示 DATETIME 与 TIMESTAMP 数据类型在时区更改后数据的差异,供大家选择时间类型做个参考,以及出现类似问题如何修正数据。

    2 创建演示表

    CREATE TABLE `test_time_zone` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `datetime1` DATETIME NULL,
      `timestamp1` TIMESTAMP NULL,
      `datetime2` DATETIME NULL,
      `timestamp2` TIMESTAMP NULL,
      PRIMARY KEY (`id`));
    

    3 设置时区为 UTC

    • 为了演示效果,临时修改时区配置,当连接断开后临时配置失效;
    // 查询当前时区
    show variables like "%time_zone%";
    
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | CST    |
    | time_zone        | +08:00 |
    +------------------+--------+
    
    // 设置为 UTC 时区
    set time_zone = '+0:00';
    
    show variables like "%time_zone%";
    
    select now();
    
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | CST    |
    | time_zone        | +00:00 |
    +------------------+--------+
    
    +---------------------+
    | now()               |
    +---------------------+
    | 2020-06-03 14:46:16 |
    +---------------------+
    

    4 插入测试数据

    insert into test_time_zone 
    (datetime1, timestamp1, datetime2, timestamp2) 
    values 
    (str_to_date('2020-06-02 22:47:06','%Y-%m-%d %H:%i:%s'), str_to_date('2020-06-02 22:47:06','%Y-%m-%d %H:%i:%s'), now(), now());
    

    5 查询结果

    • now() 函数值依赖数据库时区配置;
    • str_to_date() 函数不依赖数据库时区配置;
    select * from test_time_zone \G;
    
    *************************** 1. row ***************************
            id: 1
     datetime1: 2020-06-02 22:47:06
    timestamp1: 2020-06-02 22:47:06
     datetime2: 2020-06-03 14:46:42
    timestamp2: 2020-06-03 14:46:42
    

    6 修改时区 UTC+8

    set time_zone = '+8:00';
    
    show variables like "%time_zone%";
    
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | CST    |
    | time_zone        | +08:00 |
    +------------------+--------+
    

    7 查询数据变化

    • TIMESTAMP 类型的数据随着时区而改变;
    • DATETIME 类型的数据始终保持不变;
    select * from test_time_zone \G;
    
    *************************** 1. row ***************************
            id: 1
     datetime1: 2020-06-02 22:47:06
    timestamp1: 2020-06-03 06:47:06
     datetime2: 2020-06-03 14:46:42
    timestamp2: 2020-06-03 22:46:42
    

    8 插入测试数据

    insert into test_time_zone (datetime1, timestamp1, datetime2, timestamp2) 
    values 
    (str_to_date('2020-06-02 22:47:06','%Y-%m-%d %H:%i:%s'), str_to_date('2020-06-02 22:47:06','%Y-%m-%d %H:%i:%s'), now(), now());
    
    select * from test_time_zone \G;
    
    *************************** 1. row ***************************
            id: 1
     datetime1: 2020-06-02 22:47:06
    timestamp1: 2020-06-03 06:47:06
     datetime2: 2020-06-03 14:46:42
    timestamp2: 2020-06-03 22:46:42
    *************************** 2. row ***************************
            id: 2
     datetime1: 2020-06-02 22:47:06
    timestamp1: 2020-06-02 22:47:06
     datetime2: 2020-06-03 22:48:05
    timestamp2: 2020-06-03 22:48:05
    

    9 数据修复

    update test_time_zone
    set datetime1 = CONVERT_TZ(datetime1, '+0:00', '+8:00'),
        datetime2 = CONVERT_TZ(datetime2, '+0:00', '+8:00')
    where id = 1;
    
    select * from test_time_zone \G;
    
    *************************** 1. row ***************************
            id: 1
     datetime1: 2020-06-03 06:47:06
    timestamp1: 2020-06-03 06:47:06
     datetime2: 2020-06-03 22:46:42
    timestamp2: 2020-06-03 22:46:42
    *************************** 2. row ***************************
            id: 2
     datetime1: 2020-06-02 22:47:06
    timestamp1: 2020-06-02 22:47:06
     datetime2: 2020-06-03 22:48:05
    timestamp2: 2020-06-03 22:48:05
    

    10 小结

    • now() 函数值依赖数据库时区配置;
    • str_to_date() 函数不依赖数据库时区配置;
    • TIMESTAMP 类型的数据随着时区而改变,如果记录的日期需要让不同时区的人使用,最好使用 TIMESTAMP 类型;
    • DATETIME 类型的数据始终保持不变,时区修改后,数据需要修复;
    展开全文
  • mysql datetime与timestamp区别

    万次阅读 2015-08-26 21:49:38
    1、与时区无关,存入的是什么值就是什么值,不会根据当前时区进行转换 2、从mysql 5.6.4中,可以存储小数片段,最多到小数点后6位,显示时格式为 yyyy-MM-dd HH:mm:ss[.222222] mysql5.5中,没有小数片段。所以,我...

     

    datetime: 

    1、保存格式为YYYYMMDDHHMMSS(年月日时分秒)的整数,所以,它与时区无关,存入的是什么值就是什么值,不会根据当前时区进行转换。

    2、从mysql 5.6.4中,可以存储小数片段,最多到小数点后6位,显示时格式为 yyyy-MM-dd HH:mm:ss[.222222]

          mysql5.5中,没有小数片段,精确到秒。所以,我再从5.6版本迁移到5.5时,因生成的sql中datetime(6)有小数片段,无法导入。

    3、存储范围:从1000-01-01 00:00:00 到'9999-12-31 23:59:59'

    4、长度,8个字节,datetime(n),n不是存储长度,而是显示的小数位数,即使小数位数是0,存储是也是存储的6位小数,仅仅显示0位而已;要想显示小数,设置datetime(n),n=3显示小数点后3位,毫秒,n=6显示小数点后6位,微秒。

     

    timestamp:

    1、存入的是自1970-01-01午夜(格林尼治标准时间)以来的秒数,它和unix时间戳相同。所以它与时区有关,查询时转为相应的时区时间。比如,存储的是1970-01-01 00:00:00,客户端是北京,那么就加8个时区的小时1970-01-01 08:00:00。

    2、有小数片段,至少从5.5就开始有

    3、存储范围:'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' 

    4、可以当做时间戳使用,在更新时,自动更新,这一列只能由系统自动更新,不能由sql更新,这个在乐观锁时有广泛的应用

    6、长度,4字节,因为存储长度的原因,决定了它支持的范围的比datetime的要小

    7、显示时,显示日期和时间

     

    datetime和timestamp都可以当作时间戳使用

    datetime和timestamp都可以设置默认值,并且在其他列值更新时更新为当前时间,DEFAULT  CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP,这俩子句可以一起使用,顺序无所谓,一起使用的意思是,默认值是当前时间并且在其他列更新值时,此列更新为当前时间。

    ALTER TABLE `mytest`.`date_time_test` 
    CHANGE COLUMN `col2` `col2` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ;

     

    date

    date,时分秒都存储了,但只显示日期。对应Java中的java.sql.Date

     

     

    datetime与时区无关、timestamp与时区有关

    1、查看当前时区,并创建表test_date,一个是timestamp列,一个是datetime列

     

    2、插入两条数据,相同的时间。修改时区为0时区(格林尼治时区)后,查看时间,发现timestamp改变了,datetime没变。

     

     

    总结

    datetime、timestamp精确度都是秒,datetime与时区无关,存储的范围广(1001-9999),timestamp与时区有关,存储的范围小(1970-2038)。

    展开全文
  • I have a MySQL database that is set to a local time zone. Times are inserted as UTC time although the database uses a different time zone. I would like to change the time zone to UTC on the MySQL serv...

    I have a MySQL database that is set to a local time zone. Times are inserted as UTC time although the database uses a different time zone. I would like to change the time zone to UTC on the MySQL server. I have found documentation on how to do this, but am reluctant as I do not know if that would also change the values that are already stored in the Database.

    My Question: Will changing the time zone of a MySQL server also change the values that are already stored?

    解决方案

    In principle it shouldn't. There are various reasons why it shouldn't change, depending on the type of your values : mostly DATETIME and TIMESTAMP.

    DATETIME values are never converted, so they are independent of the time zone.

    TIMESTAMP values are converted (direct quote from the manual here --- I assume you have a fairly recent version of MySQL) "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. 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." (from http://dev.mysql.com/doc/refman/5.5/en/datetime.html).

    So in both cases the data actually stored on the server does not change (which is as it should be), but the values that your queries will show may be different before and after.

    展开全文
  • mysql datetime timestamp范围 timestamp支持时间从1970-01-01 08:00:01 到 2038-01-19 11:14:07 datetime 支持时间从 0000-00-00 00:00:00 到 9999-12-31 23:59:59 虽然0000-00-00 00:00:00 没有什么意义 大...
  • mysql datetime与timestamp

    2018-02-07 12:10:24
    1、保存格式为YYYYMMDDHHMMSS(年月日时分秒)的整数,所以,它与时区无关,存入的是什么值就是什么值,不会根据当前时区进行转换。 2、从mysql 5.6.4中,可以存储小数片段,最多到小数点后6位,显示时格式为
  • is there a way to convert from unix timestamp to GMT in mysql while running the query itself??My query is as follows:SELECTr.name, r.network, r.namestring, i.name, i.description, r.rid, i.id, d.unixti...
  • MySQL DateTime和TimeStamp的区别 DateTime TimeStamp 存储大小 8个字节 4个字节 时区 不受时区影响 格林尼治时间到现在的秒数,受时间影响 存储方式 不做任何改变,基本上是原样输出 把客户端插入的...
  • 一、如何设置 mysql 时区 1、命令 1)查时区:show variables like '%time_zone%' 返回有2行记录,要看time_zone变量的值,不需要看system_time_zone。 若值为SYSTEM表示取值跟system_time_zone保持一致。 system_...
  • I got two fields : time_scan_start and time_scan_end (timestamp field).When I open my connection to MySQL with PDO, I use SET NAMES utf8,time_zone = "+0:00";.Now, when I do a query in MySQL, do I have...
  • On one server, when I run:mysql> select now();+---------------------+| now() |+---------------------+| 2009-05-30 16:54:29 |+---------------------+1 row in set (0.00 sec)On another se...
  • timestamp 支持数据库级UTC 时区 datetime 不支持 timestamp占4个字节 datetime占8个字节 timestamp所能存储的时间范围为:'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999' ** 当timestamp 格式有...
  • Int、Datetime 和 Timestamp 时区相关性分析
  • 问题: 查询不同数据库上表中记录时间差距8小时。 昨天协助其他地区同事解决客户查询到不同...这个参数,可以在通过mysqld命令启动数据库的时候加上参数 --default-time-zone=timezone来设置时区, 也可以通过m...
  • 【转载】:MySQL中有关TIMESTAMP和DATETIME的总结 1.datetime 和 timestamp 的相同点  两者都可以用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。 2. datetime 和 timestamp 的不同点 2.1 存储方式  ...
  • mysql 5.7.20 为例 一直以来,理解有偏差,作此记录,纠正 一、DATETIME和TIMESTAMP 都有高达微秒(6位)的精度 范围 DATETIME 1000-01-01 00:00:00.000000 9999-12-31 23:59:59.999999 TIMESTAMP 1970-01-01...
  • <p>I want to create a script/query, in mysql, that would search my database for tables with <code>datetime</code> fields and then convert the values there to UTC. <p>A script that would do this ...
  • 最近,笔者在做项目的时候,发现了一件特别奇怪的事情。...这是因为Mysql 使用系统默认时区,东八区。 而Tomcat java使用UTC时区进行处理业务逻辑。 System.out.println(System.getProperty("user.ti...
  • 最近的项目需要获取自定义的date对象以及存储到mysql中 感觉date是一个每个人都会遇到的坑 我简要地讲下吧 问题描述: 我们查看java.util.Date源码,构造方法中除了用毫秒数生成date对象之外,其他的构造方法基本都...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 11,922
精华内容 4,768
关键字:

MySQLdatetime时区