Oracle全球化 —— 时间类型、时区与时间函数

最近遇到一个表中数据时区不对的问题，排查思路一般是看表字段类型、看时区以及看插入时间的方法。Oracle官方文档《Database Globalization Support Guide》里有很详细的介绍，归纳学习一下。一、时间类型Oracle里的时间类型分两大类 ——Datetime 和 Interval Data Types，本文主要关注第一类Datetime。Datet...

Hehuyi_In

4707人浏览 · 2020-03-15 20:31:11

Hehuyi_In · 2020-03-15 20:31:11 发布

最近遇到一个表中数据时区不对的问题，排查思路一般是看表字段类型、看时区以及看插入时间的方法。Oracle官方文档《Database Globalization Support Guide》里有很详细的介绍，归纳学习一下。

一、时间类型

Oracle里的时间类型分两大类 —— Datetime 和 Interval Data Types，本文主要关注第一类Datetime。

Datetime又可以分为四类，其中与时区有关的是后两类：

DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE

1. DATE

存储日期+时间，精确到秒，不存储时区和地区信息。输出格式和语言由 NLS_DATE_FORMAT和NLS_DATE_LANGUAGE 两个初始化参数决定。如果查询时不指定这两个参数也不进行类型转换，会按默认格式输出。

SQL> select sysdate from dual;

SYSDATE
-------------------
2014-02-12 01:12:18

--oracle 修改默认日期格式
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

2. TIMESTAMP

DATE类型的扩展，存储日期+时间，可精确到秒后0~9位小数点（默认是6），也不存储时区和地区信息。输出格式和语言由 NLS_TIMESTAMP_FORMAT和NLS_DATE_LANGUAGE 两个初始化参数决定。如果查询时不指定这两个参数也不进行类型转换，会按默认格式输出。

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
12-FEB-14 01.14.12.945256 AM

SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SSXFF';
Session altered.

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
2014-02-12 01:28:31.652888

也可以改对应环境变量

3. TIMESTAMP WITH TIME ZONE

TIMESTAMP类型的扩展，存储日期+时间，可精确到秒后0~9位小数点（默认是6），存储时区（或时区和地区）信息。此类型的数据在保存到数据库时带有当前客户端的session timezone，无论在什么时区查看这些数据，数据都不会随时区而变化。

create table t1 (id number,time timestamp with time zone);  --创建t1表，其中time 列的数据类型是timestamp with time zone
Table created.

select sessiontimezone from dual; --当前客户端的session timezone 是 -8:00
SESSIONTIMEZONE
---------------------------------------------------------------------------
-08:00

insert into t1 values(1,timestamp '2014-02-12 02:00:00');  --向t1表中插入一条数据  
1 row created.

select * from t1;  --查看t1表，其中time列带时区显示，并且时区为数据被插入时的session timezone
        ID     TIME
----------   ---------------------------------------------------------------------------
         1     2014-02-12 02:00:00.000000 -08:00

alter session set time_zone='-6:00';  --修改当前客户端的session timezone为 -6:00
Session altered.

select * from t1;    --再次查看t1表，其中time列数据无变化
        ID     TIME
----------   ---------------------------------------------------------------------------
         1     2014-02-12 02:00:00.000000 -08:00

4. TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP类型的另一种扩展，存储日期+时间，可精确到秒后0~9位小数点（默认是6），不存储时区信息，而是将客户端输入的时间基于database timezone转换后存入数据库（这也就是database tmiezone设置的意义所在，作为TIMESTAMP WITH LOCAL TIME ZONE类型的计算标尺)。当用户查询此类型数据时，Oracle会把数据再转为用户session的时区时间返回给用户。

客户端A时区时间 -> 数据库database tmiezone设置的时区时间 -> 客户端B时区时间

create table t2(id number,time timestamp with local time zone);  -- 创建t2表，其中time列为TIMESTAMP WITH LOCAL TIME ZONE
Table created.

insert into t2 values(1,timestamp '2014-02-12 02:10:00 -8:00');  --在t2表插入数据指定时区为-8:00，实际在保存到数据库时转化为基于database timezone的时间保存    
1 row created. 

select sessiontimezone from dual;  --当前客户端的session timezone 为 -6:00
SESSIONTIMEZONE
---------------------------------------------------------------------------
-06:00

select * from t2;  --查看时oracle将数据转换成当前客户端session timezone的时间
        ID      TIME
----------   ---------------------------------------------------------------------------
         1       2014-02-12 04:10:00.000000

5. 时间类型的选择

DATE：需要的时间精度不高，不需要保存时区/地区信息
TIMESTAMP：需要的时间精度高，不需要保存时区/地区信息
TIMESTAMP WITH TIME ZONE：需要保存时区/地区信息。比如需要精确记录每一笔交易的时间和地点（时区），看它是在当地几点发生的
TIMESTAMP WITH LOCAL TIME ZONE：不关心操作发生的地点，只关心操作是在你当前所在时区几点发生的。比如有一部电视剧在日本时间十点开播，但其实我只关心在中国时间几点能一起追直播，对我来说最方便的就是一查数据库直接告诉我它在中国时间九点开播。

二、时区

其实根据前面一节已经知道了，Oracle时区分两种 —— 数据库时区和会话时区

1. 数据库时区

作为TIMESTAMP WITH LOCAL TIME ZONE类型的计算标尺。

查询方法

SELECT dbtimezone FROM DUAL;

设置方法

可以在CREATE DATABASE 时用 SET TIME_ZONE子句指定。

CREATE DATABASE db01
...
SET TIME_ZONE='Europe/London';
-- 或者
CREATE DATABASE db01
...
SET TIME_ZONE='-05:00';

也可后期修改（重启DB生效）

ALTER DATABASE SET TIME_ZONE='Europe/London';
--或者
ALTER DATABASE SET TIME_ZONE='-05:00';

2. 会话时区

当前sql会话所在时区，默认是服务器操作系统所在时区。

查询方法

SELECT sessiontimezone FROM DUAL;

设置方法

可以设置操作系统的 ORA_SDTZ 环境变量

setenv ORA_SDTZ 'OS_TZ'  #默认
setenv ORA_SDTZ 'DB_TZ'
setenv ORA_SDTZ 'Europe/London'
setenv ORA_SDTZ '-05:00'

也可以用sql命令设置

ALTER SESSION SET TIME_ZONE=local; -- 相当于os
ALTER SESSION SET TIME_ZONE=dbtimezone;
ALTER SESSION SET TIME_ZONE='Asia/Hong_Kong';
ALTER SESSION SET TIME_ZONE='+10:00';

三、时间相关函数

Datetime函数可操作 date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE) 及 interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) 类型的值。

1. Datetime Functions Designed for the DATE Data Type

Function	Description
`ADD_MONTHS`	Retur ns the date `d` plus `n` months `SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20080818','YYYYMMDD'),2), 'YYYY-MM-DD') FROM DUAL; SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL`
`LAST_DAY`	Returns th e last day of the month that contains `date` `SELECT LAST_DAY(SYSDATE) FROM DUAL; SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -2)) FROM DUAL;`
`MONTHS_BETWEEN`	Returns the number of months between `date1` and `date2` 返回date2与date1间相隔的月份数 `SELECT MONTHS_BETWEEN(TO_DATE('2011-05-03','YYYY-MM-DD'), TO_DATE('2011-01-23','YYYY-MM-DD')) FROM DUAL; SELECT MONTHS_BETWEEN('19-12月-1999','19-3月-1999') mon_between FROM DUAL;`
`NEW_TIME`	Returns t he date and time in `zone2` time zone when the date and time in `zone1` time zone are `date` 查询指定时区的日期时间在另一指定时区对应的日期时间（各时区时间转换查询） `SELECT TO_CHAR(SYSDATE,'YYYY.MM.DD HH24:MI:SS') BeiJing_Time, TO_CHAR(NEW_TIME(SYSDATE,'PDT','GMT'),'YYYY.MM.DD HH24:MI:SS') LOS_ANGELS FROM DUAL;`
`NEXT_DAY`	Retur ns the date of the first weekday named by `char` that is later than `date` 返回自输入日期（参数1）开始，参数2的指定星期几对应是几号。参数2可以用全称如'monday'、可以用缩写如'wed'，也可以用数字（星期日 = 1 星期一 = 2 星期二 = 3 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7 ） `select next_day(to_date('1999.11.24','yyyy.mm.dd'),'friday') from dual; -- 返回 1999年11月26日 select next_day(to_date('1999.11.24','yyyy.mm.dd'),'wed') from dual; -- 返回 1999年11月01日 -- 1999年11月24日是星期三，第二个参数是星期五，是两天后。第二个例子由于日期正好是星期三，只能用下一个星期三日期。 SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL; -- 返回从当前时间开始，下个星期二对应是几号`
`ROUND(date)`	Retur ns `date` rounded to the unit specified by the `fmt` format model
`TRUNC(date)`	Retur ns `date` with the time portion of the day truncated to the unit specified by the `fmt` format model 为指定日期按指定格式而截去后的日期值，语法格式为TRUNC(date[,fmt]) `SELECT TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon-yyyy hh:mi am') FROM DUAL; -- '24-Nov-1999 12:00:00 am' SELECT TRUNC(TO_DATE('24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'),'hh') FROM DUAL; -- '24-Nov-1999 08:00:00 am' SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL; --返回当年第一天. SELECT TRUNC(SYSDATE,'MM') FROM DUAL; --返回当月第一天. SELECT TRUNC(SYSDATE,'D') FROM DUAL; --返回当前星期的第一天. SELECT TRUNC(SYSDATE,'DD') FROM DUAL; --返回当前年月日`

2. Additional Datetime Functions

Datetime Function	Description
`CURRENT_DATE`	Returns the current date in the session time zone in a value in the Gregorian calendar, of the `DATE` data type 以date类型返回当前会话时区中的当前日期 `SELECT CURRENT_DATE FROM DUAL;`
`CURRENT_TIMESTAMP`	Returns the c urrent date and time in the session time zone as a `TIMESTAMP WITH TIME ZONE` value 以timestamp with time zone类型返回当前会话时区中的当前时间 `SELECT CURRENT_TIMESTAMP FROM DUAL;`
`DBTIMEZONE`	Returns the value of the database time zone. The value is a time zone offset or a time zone region name 返回数据库时区 `SELECT DBTIMEZONE FROM DUAL;`
`EXTRACT` (datetime)	Extracts an d returns the value of a specified datetime field from a datetime or interval value expression 提取指定日期时间的指定部分，比如年、月、日、小时、分钟等 `SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear, EXTRACT(MONTH FROM OrderDate) AS OrderMonth, EXTRACT(DAY FROM OrderDate) AS OrderDay FROM Orders; --返回 OrderYear OrderMonth OrderDay 2008 12 29`
`FROM_TZ`	Conve rts a `TIMESTAMP` value at a time zone to a `TIMESTAMP WITH TIME ZONE` value
`LOCALTIMESTAMP`	Returns the current date and time in the session time zone in a value of the `TIMESTAMP` data type 以timestamp类型返回当前会话中的日期和时间 `SELECT LOCALTIMESTAMP FROM DUAL;`
`NUMTODSINTERVAL`	Convert s number `n` to an `INTERVAL DAY TO SECOND` literal
`NUMTOYMINTERVAL`	Conv erts number `n` to an `INTERVAL YEAR TO MONTH` literal
`SESSIONTIMEZONE`	Retur ns the value of the current session's time zone 返回当前会话时区，针对当前会话，可以在会话级改变 `SELECT SESSIONTIMEZONE FROM DUAL; ALTER SESSION SET TIME_ZONE = '8:00'; SELECT SESSIONTIMEZONE FROM DUAL;`
`SYS_EXTRACT_UTC`	Extr acts the UTC from a datetime with time zone offset
`SYSDATE`	Returns the date and time of the operating system on which the database resides, taking into account the time zone of the database server's operating system that was in effect when the database was started
`SYSTIMESTAMP`	Return s the system date, including fractional seconds and time zone of the system on which the database resides
`TO_CHAR` (datetime)	Converts a datetime or interval value of `DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, or `TIMESTAMP WITH LOCAL TIME ZONE` data type to a value of `VARCHAR2` data type in the format specified by the `fmt` date format
`TO_DSINTERVAL`	Converts a ch aracter string of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of `INTERVAL DAY TO SECOND` data type
`TO_NCHAR` (datetime)	Converts a datetime or interval value of `DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, `TIMESTAMP WITH LOCAL TIME ZONE`, `INTERVAL MONTH TO YEAR`, or `INTERVAL DAY TO SECOND` data type from the database character set to the national character set
`TO_TIMESTAMP`	Convert s a character string of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of `TIMESTAMP` data type
`TO_TIMESTAMP_TZ`	Converts a c haracter string of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of the `TIMESTAMP WITH TIME ZONE` data type
`TO_YMINTERVAL`	Conve rts a character string of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of the `INTERVAL YEAR TO MONTH` data type
`TZ_OFFSET`	Returns the time zone offset that corresponds to the entered value, based o n the date that the statement is executed

3. Time Zone Conversion Functions

Time Zone Function	Description
`ORA_DST_AFFECTED`	Enables yo u to verify whether the data in a column is affected by upgrading the DST rules from one version to another version
`ORA_DST_CONVERT`	Enables you to upgrade your TSTZ column data from one version to another
`ORA_DST_ERROR`	Enables you to verify that there are no errors when upgrading a datetime value