一、时区相关概念（数据库）
1、可能涉及的3个时区概念
a.UTC时间，标准时区,其他地区的时区根据时间与UTC的差距，做偏移量，如New York晚于UTC 5小时，则为-5:00。
Oracle建议将数据库时区设置为UTC
b.数据库时区，安装数据库的位置所在的时区，可通过函数DBTIMEZONE获取
c.会话时区，用户所在的时区，可通过函数SESSIONTIMEZONE
2、与日期和时区相关的数据类型：
DATE
TIMESTAMP
同DATE类型，但是增加了含有小数点的秒，提高精度
TIMESTAMP WITH TIME ZONE
类似TIMESTAMP，但附加了TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR信息。
TIMESTAMP WITH LOCAL TIME ZONE
与TIMESTAMP，不包含时区相关的数据，当查询TIMESTAMP WITH LOCAL TIME ZONE类型数据时，数据将以用户本地的时区，展示给用户。
3、TIME ZONE的两种保存方式：
通过时区名保存，如'Asia/Shanghai'、
通过本地时间与UTC时间的偏移量保存，如'+08:00'
4、数据库中跟踪时区的两个变量：
Database time zone、
Session time zone
1）查看
SQL> select dbtimezone,sessiontimezone from dual;
DBTIMEZONE SESSIONTIMEZONE
---------- ---------------------
+00:00 +08:00
2）改变数据库时区
SQL> alter database set time_zone='+01:00';
alter database set time_zone='+01:00'
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns
注：如果数据库中含有包含TIMESTAMP WITH LOCAL TIME ZONE类型列的表，则无法改变数据库时区（database time zone）
以下查询将定位这样的表
SQL> SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE LIKE '%LOCAL TIME_ZONE%'
ORDER BY OWNER, TABLE_NAME, COLUMN_NAME;
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE
------- ------------ --------------- --------------------------------
OE ORDERS ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE
改变会话时区
SQL> alter session set time_zone='+08:00';
Session altered
小建议：当应用程序需要处理多个时区和相关的数据类型时，通常将数据库的时区设置为UTC，而不必考虑数据库服务器实际所处位置的本地时区。
二、时区函数
1）DBTIMEZONE
语法：DBTIMEZONE
参数：无参数
返回：返回字符类型数据
作用：返回数据库所在位置的时区
实例：
返回当前数据的时区，根据结果可知，当前数据库的时区被设置为UTC
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+00:00
2) SESSIONTIMEZONE
语法：SESSIONTIMEZONE
参数：无参数
返回：返回字符类型数据
作用：返回当前会话的时区
实例：
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
------------------------------------------
+08:00
3) CURRENT_DATE、CURRENT_TIMESTAMP
语法：CURRENT_DATE, CURRENT_TIMESTAMP(t)
参数：CURRENT_DATE无参数；
CURRENT_TIMESTAMP中的参数t，指定本地时区小数点后的精度，范围1-9。可选项，默认为6
返回：CURRENT_DATE返回日期类型的数据；
CURRENT_TIMESTAMP返回TIMESTAMP WITH TIME ZONE类型数据
作用：CURRENT_DATE返回当前日期
CURRENT_TIMESTAMP返回会话的时间戳，包含时区信息
实例：
SQL> select current_date,current_timestamp from dual;
CURRENT_DATE CURRENT_TIMESTAMP
------------ --------------------------------------
2011-4-19 14 19-APR-11 02.23.58.545048 PM +08:00
注：current_date返回的结果其实也包含时分秒（不包含精度），可通过to_char转换函数来获取
如：
SQL> select to_char(current_date,'yyyy-mm-dd hh24:mi:ss'),current_timestamp from dual;
TO_CHAR(CURRENT_DATE,'YYYY-MM- CURRENT_TIMESTAMP
------------------------------ ---------------------------------------
2011-04-19 14:28:22 19-APR-11 02.28.22.258097 PM +08:00
4) LOCALTIMESTAMP
语法：LOCALTIMESTAMP(t)
参数：参数't'指定本地时区小数点后的精度，范围1-9。可选项，默认为6
返回：返回TIMESTAMP类型的数据，即不包含时区信息
作用：显示用户会话的本地时间，包含年月日时分秒，及秒的精度值。
实例：
SQL> SELECT LOCALTIMESTAMP(4) FROM DUAL;
LOCALTIMESTAMP(4)
----------------------------
19-APR-11 02.32.54.3425 PM
5) SYSTIMESTAMP
语法：SYSTIMESTAMP
参数：无参数
返回：返回TIMESTAMP WITH TIME ZONE类型的操作系统时间，含精度
作用：返回TIMESTAMP WITH TIME ZONE类型的系统日期，包含秒的精度
实例：
SQL> select systimestamp from dual;
SYSTIMESTAMP
-----------------------------------------
19-APR-11 02.34.15.286712 PM +08:00
6) NEW_TIME
语法：NEW_TIME(d, t1, t2)
参数：d为日期（date）类型数据
t1为时区，参见下表
t2为时区，参见下表
返回：返回日期时间型数据
作用：假设t1是记录时间d的时区，函数将返回此刻，t2时区对应的时间。
实例：
SELECT '1983-JAN-03 14:30:56' AS OLD_DATE,
TO_CHAR(
NEW_TIME(
TO_DATE('1983-JAN-03 14:30:56','RRRR-MON-DD HH24:MI:SS'),
'AST',
'HST')
,'DD-MON-RR HH:MI:SS') NEW_DATE
FROM DUAL;
OLD_DATE NEW_DATE
-------------------- ---------------------------
1983-JAN-03 14:30:56 03-JAN-83 08:30:56
注：new_time函数中，t1与t2的可选值
Time Zones Standard Time Daylight Saving Time
Atlantic AST ADT
Bering BST BDT
Central CST CDT
Eastern EST EDT
Greenwich UTC
Alaska-Hawaii HST HDT
Mountain MST MDT
Newfoundland NST
Pacific PST PDT
Yukon YST YDT
三、时区转换函数
1）FROM_TZ
语法：FROM_TZ(date_time,time_zone)
参数：date_time为一个日期时间型参数；
time_zone为时区，可通过'小时:分钟'来指定，如'+08:00'；也可通过时区名来指定
返回：返回TIMESTAMP WITH TIME ZONE类型数据
作用：给定时间和时区，将二者组合成TIMESTAMP WITH TIME ZONE类型数据
实例：
SQL> SELECT FROM_TZ(to_timestamp( '2012-10-12 07:45:30','yyyy-mm-dd hh24:mi:ss'), '+08:00') FROM DUAL;
或
SQL> SELECT FROM_TZ(TIMESTAMP '2012-10-12 07:45:30','+08:00') FROM DUAL;
FROM_TZ(TO_TIMESTAMP('2012-10-
--------------------------------------------------------------------------------
12-OCT-12 07.45.30.000000000 AM +08:00
SQL> SELECT FROM_TZ( TIMESTAMP '2012-10-12 07:45:30', 'Asia/Shanghai') FROM DUAL;
FROM_TZ(TIMESTAMP'2012-10-1207
--------------------------------------------------------------------------------
12-OCT-12 07.45.30.000000000 AM ASIA/SHANGHAI
2）TO_TIMESTAMP_TZ
语法：TO_TIMESTAMP_TZ(date_time, format_model, nls_parms)
参数： date_time：一个日期时间字符串
format_model：描述日期时间串date_time中各部分所表示的含义，
如date_time为'2012-04-19 15:50:30'，则format_model为'YYYY-MM-DD HH24:MI:SS'
nls_parms代表NLS参数设置，指定了如下元素的设置：
Decimal character（小数点字符）
Group separator（分组字符）
Local currency symbol（本地货币符号）
International currency symbol（国际货币符号）
可取的值为NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory
若指定nls_parms参数，则该参数为1个nls或多个nls参数。若为多个值，则多个值需要用单引号括起来。
如
' nls_currency = ''USD'' nls_numeric_characters = '',.'' '
返回：返回TIMESTAMP WITH TIME ZONE类型的数据
作用：将给定的日期时间串date_time转换为TIMESTAMP WITH TIME ZONE类型数据
实例：
SQL> SELECT TO_TIMESTAMP_TZ('17-04-2013 16:45:30','DD-MM-RRRR HH24:MI:SS') "Time" FROM DUAL;
Time
--------------------------------------------------
17-APR-13 04.45.30.000000000 PM +08:00
SQL> SELECT TO_TIMESTAMP_TZ('16:45:30 2012-04-19','HH24:MI:SS YYYY-MM-DD') "Time" FROM DUAL;
Time
--------------------------------------------------
19-APR-12 04.45.30.000000000 PM +08:00
3）CAST
语法：CAST(expression AS date_type)
参数：expression是一个表达式；date_type是一个数据类型
返回：返回一个date_type参数指定的数据类型
作用：将表达式expression转换为date_type参数指定的数据类型
实例：
如将'19-JAN-10 11:35:30'转换为TIMESTAMP WITH LOCAL TIME ZONE类型的数据
SQL> select CAST(
'19-JAN-2012 11:15:20 AM' AS TIMESTAMP
WITH LOCAL TIME ZONE
)
FROM DUAL;
CAST('19-JAN-201211:15:20 AM'AS
----------------------------------------
19-JAN-12 11.15.20.000000 AM
SQL> SELECT CAST(
TO_TIMESTAMP('2012-01-19 11:15:20','YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP
WITH LOCAL TIME ZONE
)
FROM DUAL;
CAST(TO_TIMESTAMP('2012-01-191
--------------------------------------------------------------------------------
19-JAN-12 11.15.20.000000 AM
4）EXTRACT
语法：EXTRACT( format_model FROM time_stamp_e)
参数：format_model设置格式化参数，可取值为：
YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,
TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION,
TIMEZONE_ABBR;
time_stamp_e是一个日期时间表达式
返回：如果提取TIMEZONE_REGION or TIMEZONE_ABBR ，则返回字符串；
如果提取其他类型，则返回数值型数据。
作用：根据format_model设置，从表达式time_stamp_e参数指定的日期时间表达式提取相应数据
实例：
SQL> SELECT EXTRACT(
MINUTE FROM
FROM_TZ( TIMESTAMP '2012-10-12 07:45:30', 'Asia/Shanghai')
) "MINUTE"
FROM DUAL;
MINUTE
----------
45
SQL> SELECT EXTRACT(
TIMEZONE_REGION FROM
FROM_TZ( TIMESTAMP '2012-10-12 07:45:30', 'Asia/Shanghai')
) "TIMEZONE_REGION"
FROM DUAL;
TIMEZONE_ABBR
-------------
CST
SQL> SELECT EXTRACT(
TIMEZONE_REGION FROM
FROM_TZ( TIMESTAMP '2012-10-12 07:45:30', 'Asia/Shanghai')
) "TIMEZONE_REGION"
FROM DUAL;
TIMEZONE_REGION
--------------------------------
Asia/Shanghai
5）SYS_EXTRACT_UTC
语法：SYS_EXTRACT_UTC(date_with_timezone)
参数：date_with_timezone是任意一个包含时区偏移（time zone offset）或时区名（time zone region name）信息的日期时间值
返回：
作用：根据date_with_timezone指定的包含时区信息的日期时间值，计算此刻，格林威治时间（UTC）
实例：
如东八区（'+08:00'）的日期及时间为：2012年4月19日 17点30分时，计算此刻的格林威治时间(UTC) ：
SQL> SELECT SYS_EXTRACT_UTC(TIMESTAMP '2012-04-19 17:30:00 +08:00 ') "CURRENT UTC TIME"
FROM DUAL;
CURRENT UTC TIME
------------------------------------
19-APR-12 09.30.00.000000000 AM
四、换函数中，支持的附加选项
1）AT TIME ZONE
SELECT TO_TIMESTAMP('2012-MAY-24 02:00:00','RRRR-MON-DD HH24:MI:SS')
AT TIME ZONE DBTIMEZONE "DB Time"
FROM DUAL
语法：AT TIME ZONE time_zone_name
参数：time_zone_name可取的值为：
a.DBTIMEZONE;
b.SESSIONTIMEZONE;
c.时区的偏移值（time zone offset），如：'+08:00'或'-08:00'
d.时区的名字(time zone region name)，如：'Asia/Shanghai'
e.可以得到以上4类值的表达式
作用：根据AT TIME ZONE指定的时区，将查询语句中的时间转换为AT TIME ZONE指定时区的时间
备注：针对以下类型时间有效
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
实例：
SQL> SELECT TO_TIMESTAMP('2012-04-19 17:40:00','YYYY-MM-DD HH24:MI:SS')
AT TIME ZONE DBTIMEZONE
AS DBTIMEZONE
FROM DUAL;
DBTIMEZONE
-----------------------------------------------
19-APR-12 09.40.00.000000000 AM +00:00
SQL> SELECT TO_TIMESTAMP('2012-04-19 17:40:00','YYYY-MM-DD HH24:MI:SS')
AT TIME ZONE SESSIONTIMEZONE
AS SESSIONTIMEZONE
FROM DUAL;
SESSIONTIMEZONE
------------------------------------------------
19-APR-12 05.40.00.000000000 PM +08:00
SQL> SELECT TO_TIMESTAMP('2012-04-19 17:40:00','YYYY-MM-DD HH24:MI:SS')
AT TIME ZONE '+08:00'
AS "OFFSET(+8:00)"
FROM DUAL;
OFFSET(+8:00)
------------------------------------------------
19-APR-12 05.40.00.000000000 PM +08:00
SQL> SELECT TO_TIMESTAMP('2012-04-19 17:40:00','YYYY-MM-DD HH24:MI:SS')
AT TIME ZONE 'Asia/Shanghai'
AS "REGION NAME(ASIA/SHANGHAI)"
FROM DUAL;
REGION NAME(ASIA/SHANGHAI)
--------------------------------------------------------------------------------
19-APR-12 05.40.00.000000000 PM ASIA/SHANGHAI
2）AT LOCAL
将给定的含时区的日期时间数据转换为会话时区的日期时间值
如将UTC时间2012-04-19 01:00:00 AM转换为当前会话时区的日期时间值
1）确认会话时区
SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIM
----------
+08:00
SQL> SELECT '2012-04-19 01:00:00 AM' AS "UTC TIME",
2 FROM_TZ(TO_TIMESTAMP('2012-04-19 01:00:00','YYYY-MM-DD HH24:MI:SS'),'+00:00')
3 AT LOCAL
4 AS "SESSIONTIMEZONE TIME" FROM DUAL;
UTC TIME SESSIONTIMEZONE TIME
-------------------------- -----------------------------------------------
2012-04-19 01:00:00 AM 19-APR-12 09.00.00.000000000 AM +08:00
注释：第2行，构造一个UTC的时间，即时区为（+00:00）的时间：2012-04-19 01:00:00'
第3行，AT LOCAL指定将构造的时间，转换为会话时区（SESSIONTIMEZONE）的时间
第4行，为结果列指定别名
验证：'+08:00'时区的时间为2012-04-19 09:00:00 AM时，UTC时间是否为2012-04-19 01:00:00 AM
SQL> SELECT '2012-04-19 09:00:00 AM' AS "SESSIONTIMEZONE TIME" ,
2 FROM_TZ(TO_TIMESTAMP('2012-04-19 09:00:00','YYYY-MM-DD HH24:MI:SS'),'+08:00')
3 AT TIME ZONE '+00:00'
4 AS "UTC TIME" FROM DUAL;
SESSIONTIMEZONE TIME UTC TIME
-------------------------- -----------------------------------------------
2012-04-19 09:00:00 AM 19-APR-12 01.00.00.000000000 AM +00:00
注释：第2行，构造一个'08:00'时区的包含时区信息的日期时间数据，时间为2012-04-19 09:00:00 AM
第3行，通过AT TIME ZONE指定将构造的时间，转换为UTC（+00:00）时区的时间
第4行，为结果列指定别名。
结论：得到UTC时间为2012-04-19 01:00:00 AM；该时刻，数据库会话时区（SESSIONTIMEZONE），
即'+08:00'时区的时间为2012-04-19 09:00:00 AM
转载自：http://blog.itpub.net/25264937/viewspace-692977/
2101
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
