MYSQL和ORACLE时区设置比较

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: MYSQL: 注意时区会影响TIMESTAMP的取值,默认为系统时区为TIME_ZONE=SYSTEM, 动态可以修改 set global  time_zone = '+8:00';  然后 my.
MYSQL:
注意时区会影响TIMESTAMP的取值,默认为系统时区为TIME_ZONE=SYSTEM,
动态可以修改
set global  time_zone = '+8:00'; 

然后
my.cnf加上,永久修改
default-time_zone = '+8:00' 

The current time zone. This variable is used to initialize the time zone for each client that
connects. By default, the initial value of this is 'SYSTEM'(which means, “use the value of
system_time_zone”). 
也就是说每个链接都会使用这个参数作为他的默认时区,而TIMESTMAP是根据客户端的时区不同
而不同,所以如果如果这个参数设置有误会导致TIMESTAMP时间出现问题

MYSQL的测试:
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-12 12:10:13 |
+---------------------+
1 row in set (0.00 sec)


mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2015-06-12 12:10:18 |
+---------------------+
1 row in set (0.00 sec)


mysql> select current_timestamp from dual;
+---------------------+
| current_timestamp   |
+---------------------+
| 2015-06-12 12:10:46 |
+---------------------+
1 row in set (0.00 sec)


mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)


mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2015-06-12 04:11:01 |
+---------------------+
1 row in set (0.00 sec)


mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-12 04:11:04 |
+---------------------+
1 row in set (0.00 sec)


mysql> select current_timestamp from dual;
+---------------------+
| current_timestamp   |
+---------------------+
| 2015-06-12 04:11:06 |
+---------------------+
1 row in set (0.01 sec)

可见MYSQL的NOW(),SYSDATE(), current_timestamp 均跟着客户端时区走的。

oracle:
另外说一下ORACLE的时区问题,ORACLE时区分为
dbtimezone和sessiontimezone
其中DBTIMEZONE只和TIMESTAMP WITH LOCAL TIME ZONE有关,在TIMESTAMP WITH LOCAL TIME ZONE类型存入数据库中,实际上是转换为DBTIMEZONE的时间,取出的时候
自动加上客户端的SESSIONTIMEZONE的偏移量,文档如下:

TimeStamp with Local Time Zone (TSLTZ) data stores internally the time converted to/from the database timezone (see point 3) from the timezone specified at insert/select time. 

Note that the data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data, the current DBTIMZONE is used. When users retrieve the data, Oracle Database returns it in the users' local session time zone from the current DBTIMEZONE.


而其他的时间类型和DBTIMEZONE无关,这也是为什么有了TIMESTAMP WITCH LOCAL TIME ZONE修改DBTIMEZONE不行的原因,因为如果修改了DBTIMEZONE会导致时间错误。
实际上MYSQL的TIMESTAMP类型和ORACLE的 TIMESTAMP WITCH LOCAL TIME ZONE类型都是根据客户端的时间来进行返回时间,但是MYSQL可以简单的设置
 time_zone参数来改变所有连接的时区,这样返回的时间能够正确。
在说明一下ORACLE的TIMESTAMP和MYSQL的TIMESTAMP完全不同,
ORACLE的TIMESTAMP是为了精确到秒后6位,
而MYSQL的TIMESTAMP是为了更少的存储单元(DATETIME为4字节,TIMESTAMP为1个字节)但是范围为 1970的某时的开始到2037年,而且会根据客户端的时区判断返回值

sessiontimezone,则影响着客户端的时区,TIMESTAMP WITCH LOCAL TIME ZONE也会跟着这个时区进行改变,其他数据类型如DATE,TIMESTAMP等不会受到影响
可以再ALTER SESSION中设置也可以设置环境变量TZ=
如:
ALTER SESSION SET TIME_ZONE = '-05:00';
或者
export TZ='Asia/Shanghai';

做个简单的实验

SQL> desc testtim;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATE1                                              TIMESTAMP(6)
 DATE2                                              TIMESTAMP(6) WITH TIME ZONE
 DATE3                                              TIMESTAMP(6) WITH LOCAL TIME ZONE
SQL> select * from testtim;

DATE1
---------------------------------------------------------------------------
DATE2
---------------------------------------------------------------------------
DATE3
---------------------------------------------------------------------------
12-JUN-15 11.40.02.000000 AM
12-JUN-15 11.40.02.000000 AM +08:00
12-JUN-15 11.40.02.000000 AM


SQL> alter SESSION SET TIME_ZONE = '-05:00';

Session altered.

SQL> select * from testtim;

DATE1
---------------------------------------------------------------------------
DATE2
---------------------------------------------------------------------------
DATE3
---------------------------------------------------------------------------
12-JUN-15 11.40.02.000000 AM
12-JUN-15 11.40.02.000000 AM +08:00
11-JUN-15 10.40.02.000000 PM


最后ORACLE中常用的取时间函数的不同:

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP, that is date time similar to CURRENT_DATE but the datatype is TIMESTAMP.

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE, that is date time similar to CURRENT_DATE but the datatype is TIMESTAMP WITH TIME ZONE.

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE. Unlike SYSDATE, which you can set to a constant using FIXED_DATE, SYSTIMESTAMP will give the system date even though FIXED_DATE is set.

"SYSDATE" and "SYSTIMESTAMP" are purely dependent on the operating system clock, hence it IS depending on the timezone information of this operating system and/or the operating system settings when the database and listener where started.

很显然 LOCALTIMESTAMP和 CURRENT_TIMESTAMP都受到客户端SESSIONTIMEZONE影响,而 SYSDATE的不受影响他返回的一定是服务器ORACLE 设置的SESSIONTIMEZONE的时间。
如果需要更改客户端的SYSDATE的取值必须
1、修改服务器下ORACLE用户的TZ
2、重启数据库

如:

export  TZ='UTC';
后查看服务端SYSDATE
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;


TO_CHAR(SYSDATE,'YY
-------------------
2015-06-12 04:06:34
按理说客户端也应该返回这个值
但是客户端任然返回

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2015-06-12 12:08:19

重启后
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2015-06-12 04:09:19

客户端正常。

总结一下:
1、ORACLE和MYSQL的timestamp不同
ORACLE的TIMESTAMP是为了精确到秒后6位,
而MYSQL的TIMESTAMP是为了更少的存储单元(DATETIME为4字节,TIMESTAMP为1个字节)但是范围为 1970的某时的开始到2037年,而且会根据客户端的时区判断返回值
MYSQL的TIMESTAMP时区敏感这点和ORACLE的TIMESTAMP WITH LOCAL TIME ZONE一致。
2、ORACLE和MYSQL的函数返回不一样
ORACLE:
LOCALTIMESTAMP和 CURRENT_TIMESTAMP都受到客户端SESSIONTIMEZONE影响 ,而 SYSDATE,SYSTIMESTAP的不受影响他返回的一定是服务器ORACLE 设置的SESSIONTIMEZONE的时间
MYSQL:
NOW(),SYSDATE(),
CURRENT_TIMESTAMP 均受到客户端连接时区影响。

3、oracle的DBTIMEZONE用处不大,只和 TIMESTAMP WITH LOCAL TIME ZONE有关。
4、为了返回一致的数据MYSQL设置TIME_ZONE参数即可因为他是每个连接都会用到的,但是ORACLE最好使用SYSDATE或者SYSTIMESTAMP来直接取服务端的SESSIONTIMEZONE下的时间。
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
14天前
|
数据采集 DataWorks 监控
DataWorks产品使用合集之mysql-cdc读取数据写入到datahub中,datahub如何转换时区
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
25 1
|
8天前
|
XML 关系型数据库 MySQL
支付系统----微信支付19---集成MyBatis-plus,数据库驱动对应的依赖版本设置问题,5没版本没有cj这个依赖,mysql驱动默认的是版本8,这里是一个父类,数据库都有,写个父类,继承就行
支付系统----微信支付19---集成MyBatis-plus,数据库驱动对应的依赖版本设置问题,5没版本没有cj这个依赖,mysql驱动默认的是版本8,这里是一个父类,数据库都有,写个父类,继承就行
|
2月前
|
关系型数据库 MySQL 数据库
mysql 设置环境变量与未设置环境变量连接数据库的区别
设置与未设置MySQL环境变量在连接数据库时主要区别在于命令输入方式和系统便捷性。设置环境变量后,可直接使用`mysql -u 用户名 -p`命令连接,而无需指定完整路径,提升便利性和灵活性。未设置时,需输入完整路径如`C:\Program Files\MySQL\...`,操作繁琐且易错。为提高效率和减少错误,推荐安装后设置环境变量。[查看视频讲解](https://www.bilibili.com/video/BV1vH4y137HC/)。
124 3
mysql 设置环境变量与未设置环境变量连接数据库的区别
|
18天前
|
关系型数据库 MySQL Linux
mysql 设置wait_timeout连接等待时间
mysql 设置wait_timeout连接等待时间
17 0
|
19天前
|
关系型数据库 MySQL Go
Mysql查看数据库时区并设置时区
Mysql查看数据库时区并设置时区
19 0
|
2月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之使用 MySQL CDC 进行数据同步时,设置 server_id 参数如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版操作报错之遇到MySQL服务器的时区偏移量(比UTC晚18000秒)与配置的亚洲/上海时区不匹配,如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
1月前
|
SQL Oracle 关系型数据库
mysql和oracle 命令行执行sql文件 数据库执行sql文件 执行sql语句
mysql和oracle 命令行执行sql文件 数据库执行sql文件 执行sql语句
44 0
|
2月前
|
关系型数据库 MySQL 数据处理
实时计算 Flink版产品使用合集之如果在 MySQL 表中为某个字段设置了默认值,并且在插入数据时指定了该字段为 NULL,那么 MySQL 是否会使用默认值来填充这个字段
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2月前
|
存储 Oracle 关系型数据库
oracle 数据库 迁移 mysql数据库
将 Oracle 数据库迁移到 MySQL 是一项复杂的任务,因为这两种数据库管理系统具有不同的架构、语法和功能。
50 0

推荐镜像

更多