数据库时区那些事儿 - Oracle的时区处理

当JVM时区和数据库时区不一致的时候,会发生什么?这个问题也许你从来没有注意过,但是当把Java程序容器化的时候,问题就浮现出来了,因为目前几乎所有的Docker Image的时区都是UTC。本文探究了Oracle及其JDBC驱动对于时区的处理方式,并尝试给出最佳实践。

先给总结

  • DATETIMESTAMP类型不支持时区转换。
  • 如果应用和Oracle的时区不一致,那么应该使用TIMESTAMP WITH LOCAL TIME ZONE
    • 对于JDBC程序来说,JVM时区和用户时区保持一致就行了。
  • 如果应用和Oracle的时区不一致,而且需要保存时区信息,那么应该使用TIMESTAMP WITH TIME ZONE
  • 格式化日期时间字符串函数TO_CHAR
    • 对于TIMESTAMP WITH TIME ZONE来说,使用TO_CHAR时要注意让它输出时区信息(TZH:TZM TZR TZD),否则结果会是截断的。
    • 对于TIMESTAMP WITH LOCAL TIME ZONE来说,使用TO_CHAR返回的结果会转换时区。
  • 当前日期时间的函数:
    • 除非必要,不要使用SYSDATESYSTIMESTAMP,这个返回的是数据库所在操作系统的时间。
    • 尽量使用CURRENT_TIMESTAMP,它返回的是TIMESTAMP WITH TIME ZONE,能够用来安全的比较时间。

日期时间类型的时区

Oracle Datetime Datatypes有这么几种:

  • DATE,保存YYYY-MM-DD HH24:MI:SS
  • TIMESTAMP,比DATE多存了fractional seconds(FF)。
  • TIMESTAMP WITH TIME ZONE,比TIMESTAMP多了时区偏移量(比如+08:00,TZH:TZM)or 时区区域名称(比如Asia/Shanghai,TZR)和夏令时标记(TZD)。
  • TIMESTAMP WITH LOCAL TIME ZONE。和TIMESTAMP类似,不过存储的数据会标准化为数据库的时区,用户获取它的时候会转换成用户时区(对于JDBC来说,就是JVM时区)。
1
2
3
4
docker run --name oracle-xe-timezone-test \
  -e ORACLE_ALLOW_REMOTE=true \
  -p 1521:1521 \
  -d wnameless/oracle-xe-11g:16.04

然后用system/oracle用户登录到oracle,执行下列sql建表:

1
2
3
4
5
6
create table test (
  date_field date,
  ts_field timestamp,
  ts_tz_field timestamp with time zone,
  ts_ltz_field timestamp with local time zone
);

为了验证这个结论,我写了一段程序来实验,这个程序做了三件事情:

  1. 使用Asia/Shanghai时区构造一个日期java.util.Date:2018-09-14 10:00:00,然后插入到数据库里。
  2. 使用Asia/Shanghai时区把这个值再查出来,看看结果。
  3. 使用Asia/Shanghai时区,获得这个字段的格式化字符串(使用DATE_FORMAT()函数)。
  4. 使用Europe/Paris时区重复第2-3步的动作。

运行程序获得以下结果:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
JVM Time Zone      : 中国标准时间
Retrieve java.util.Date from DATE column                              : 2018-09-14 10:00:00.0
Retrieve java.util.Date from TIMESTAMP column                         : 2018-09-14 10:00:00.0
Retrieve java.util.Date from TIMESTAMP WITH TIME ZONE column          : 2018-09-14 10:00:00.0
Retrieve java.util.Date from TIMESTAMP WITH LOCAL TIME ZONE column    : 2018-09-14 10:00:00.0
Retrieve formatted string from DATE column                            : 2018-09-14 10:00:00
Retrieve formatted string from TIMESTAMP column                       : 2018-09-14 10:00:00
Retrieve formatted string from TIMESTAMP WITH TIME ZONE column        : 2018-09-14 10:00:00 +08:00 ASIA/SHANGHAI CST
Retrieve formatted string from TIMESTAMP WITH LOCAL TIME ZONE column  : 2018-09-14 10:00:00
--------------------
JVM Time Zone      : 中欧时间
Retrieve java.util.Date from DATE column                              : 2018-09-14 10:00:00.0
Retrieve java.util.Date from TIMESTAMP column                         : 2018-09-14 10:00:00.0
Retrieve java.util.Date from TIMESTAMP WITH TIME ZONE column          : 2018-09-14 04:00:00.0
Retrieve java.util.Date from TIMESTAMP WITH LOCAL TIME ZONE column    : 2018-09-14 04:00:00.0
Retrieve formatted string from DATE column                            : 2018-09-14 10:00:00
Retrieve formatted string from TIMESTAMP column                       : 2018-09-14 10:00:00
Retrieve formatted string from TIMESTAMP WITH TIME ZONE column        : 2018-09-14 10:00:00 +08:00 ASIA/SHANGHAI CST
Retrieve formatted string from TIMESTAMP WITH LOCAL TIME ZONE column  : 2018-09-14 04:00:00

可以看到,DATETIMESTAMP是不支持时区转换的,实际上DATETIMESTAMP会丢弃掉时区信息。

对于TIMESTAMP WITH TIME ZONE来说,使用TO_CHAR时要注意让它输出时区信息(TZH:TZM TZR TZD),否则结果会是截断的。

对于TIMESTAMP WITH LOCAL TIME ZONE来说,使用TO_CHAR返回的结果会转换时区。

当前日期时间相关函数

Oracle和当前时间有关的函数有这么几个:

  • CURRENT_DATE,返回的是DATE类型
  • CURRENT_TIMESTAMP,返回的是TIMESTAMP WITH TIME ZONE类型
  • LOCALTIMESTAMP,返回的是TIMESTAMP类型
  • SYSDATE,返回的是DATE类型
  • SYSTIMESTAMP,返回的是TIMESTAMP类型

写了一段程序,输出结果是这样的:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
=========TEST CURRENT DATE/TIME FUNCTIONS===========
JVM Time Zone               : 中国标准时间
Test CURRENT_DATE           : 2018-09-18 10:27:23.0
Test CURRENT_TIMESTAMP      : 2018-09-18 10:27:23.880378 Asia/Shanghai
Test LOCALTIMESTAMP         : 2018-09-18 10:27:23.926375
Test SYSDATE                : 2018-09-18 02:27:23.0
Test SYSTIMESTAMP           : 2018-09-18 02:27:23.929605 +0:00
--------------------
JVM Time Zone               : 中欧时间
Test CURRENT_DATE           : 2018-09-18 04:27:45.0
Test CURRENT_TIMESTAMP      : 2018-09-18 04:27:45.429024 Europe/Paris
Test LOCALTIMESTAMP         : 2018-09-18 04:27:45.482485
Test SYSDATE                : 2018-09-18 02:27:45.0
Test SYSTIMESTAMP           : 2018-09-18 02:27:45.48582 +0:00

可以发现,CURRENT_DATECURRENT_TIMESTAMPLOCALTIMESTAMP的结果都根据客户端时区做了转换。而SYSDATESYSTIMESTAMP返回的则是数据库所在操作系统所在时区的时间。

在Oracle客户端操作时区

1
2
3
4
5
-- 查询系统时区和session时区
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;

-- 设置session时区
ALTER SESSION SET TIME_ZONE='Asia/Shanghai';

参见Setting the Database Time ZoneSetting the Session Time Zone

参考资料

相关代码

https://github.com/chanjarster/jdbc-timezone

版权

评论