Oracle时间戳真是个bt的东西.......... = =|||
Unix时间戳记是从'1970-01-01 00:00:00'GMT开始的秒数，表现为整数型。
Oracle中的时间是Date型，以下函数提供了两种时间转换的Oracle函数
（1）从Unix时间戳记转换为Oracle时间
create or replace function unix_to_oracle(in_number NUMBER) return date is
begin
return(TO_DATE('19700101','yyyymmdd') + in_number/86400 +TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))/24);
end unix_to_oracle;
（2）由Oracle时间Date型转换为Unix时间戳记
create or replace function oracle_to_unix(in_date IN DATE) return number is
begin
return( (in_date -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600);
end oracle_to_unix;
1. 随机数包
SELECT DBMS_RANDOM.VALUE
FROM DUAL;
2. 在[0..100]范围内取随机数
SELECT TRUNC (DBMS_RANDOM.VALUE (0, 100))
FROM DUAL;
3. 大于字符‘A’的10个字符随机字符串
SELECT DBMS_RANDOM.STRING ('A', 10)
FROM DUAL;
4. 单个小写随机字符
SELECT CHR (ROUND (DBMS_RANDOM.VALUE (97, 122)))
FROM DUAL;
5. 在过去10天内取随机日期
SELECT TO_DATE (TRUNC (DBMS_RANDOM.VALUE (TO_NUMBER (TO_CHAR (SYSDATE - 10,
'J'
)
),
TO_NUMBER (TO_CHAR (SYSDATE, 'J'))
)
),
'J'
)
FROM DUAL;
6. 在过去30分钟内取随机时间
SELECT (SYSDATE - 1 / 24 / 60 * 30) + DBMS_RANDOM.VALUE (1, 1800) / 3600 / 24
FROM DUAL;
随机函数
Sql代码
--创建55到100之间随机数
select dbms_random.value, dbms_random.value(55,100) from dual;
--创建2009年内的随机时间
SELECT
to_timestamp('2009-01-01 00:00:01','yyyy-mm-dd HH24:MI:SS') +DBMS_RANDOM.VALUE(1,365*24*3600)/3600/24
FROM DUAL
--随机查看前N条记录(随机读取表内容)
SELECT * FROM (SELECT * FROM chifan ORDER BY dbms_random.random) WHERE ROWNUM<=5
unix/linux 时间戳转换为标准时间格式（主要是注意时区问题）：
sql代码
select TO_DATE('19700101','yyyymmdd') + 1235728935/86400 +TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))/24 from dual，其中1235728935就是unix/linux时间戳，转换完之后就表示为 2009-2-27 18:02:15。
反过来也一样，还是要考虑时区
select (to_date('2009-2-27 18:02:15','yyyy-mm-dd hh24:mi:ss') - to_date('1970-1-1','yyyy-mm-dd'))*86400- TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600 from dual
Oracle Databse 9i数据库引入了一种新特性，可以存储时间戳。时间戳可以存储世纪、4位年、月、日、时(以24小时格式)、分、秒。与DATE类型相比，时间戳具有以下优点：
● 时间戳可以存储秒的小数位。
● 时间戳可以存储时区。
下面介绍时间戳类型。
使用时间戳类型
时间戳有3种类型，如表所示。
表 时间戳类型
|
我的例子：
create table test_timestamp (
id integer primary key,name varchar2(20),time timestamp(9)
);
insert into test_timestamp values(1,'tester1',systimestamp);
select * from test_timestamp;
接下来的几节将介绍如何使用这些时间戳。
1. 使用TIMESTAMP类型
与其他类型类似，TIMESTAMP类型也可以用来在表中定义列。下面这个语句用于创建一个表purchases_with_timestamp，该表用来存储顾客的购买记录。该表包含了一个TIMESTAMP类型的列made_on，该列用来记录顾客在何时购买了产品。注意TIMESTAMP的精度设置为4(这意味着在秒的小数点右边可以存储4位小数)：
- CREATE TABLE purchases_with_timestamp (
- product_id INTEGER REFERENCES products(product_id),
- customer_id INTEGER REFERENCES customers(customer_id),
- made_on TIMESTAMP(4)
- );
CREATE TABLE purchases_with_timestamp (
product_id INTEGER REFERENCES products(product_id),
customer_id INTEGER REFERENCES customers(customer_id),
made_on TIMESTAMP(4)
);
注意：
purchases_with_timestamp表由store_schema.sql脚本创建和填充。本章的其他表也是由该脚本创建，因此不需要输入CREATE TABLE语句或本章所示的任何INSERT语句。
要向数据库提供一个TIMESTAMP字面值，可以在一个时间值前面指定TIMESTAMP关键字，格式如下：
- TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.SSSSSSSSS'
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.SSSSSSSSS'注意在小数点后面有9个S字符，这意味着可以在字符串中为秒的小数部分指定9个数字。TIMESTAMP列中可以实际存储多少位数字取决于在定义该列时为秒的小数部分指定了多少位数字。例如，purchases_with_timestamp表的made_on列可以存储最多4位小数的秒数。如果试图向该表中添加一行秒的精度超过4位小数的记录，小数部分就会被取整。例如：
- 2005-05-13 07:15:31.123456789
2005-05-13 07:15:31.123456789将被取整为：
- 2005-05-13 07:15:31.1235
2005-05-13 07:15:31.1235
下面这个INSERT语句向purchases_with_timestamp表中添加一行记录，注意此处使用了TIMESTAMP关键字指定一个时间值：
- INSERT INTO purchases_with_timestamp (
- product_id, customer_id, made_on
- ) VALUES (
- 1, 1, TIMESTAMP '2005-05-13 07:15:31.1234'
- );
INSERT INTO purchases_with_timestamp (
product_id, customer_id, made_on
) VALUES (
1, 1, TIMESTAMP '2005-05-13 07:15:31.1234'
);
下面这个查询对purchases_with_timestamp表进行检索：
- SELECT *
- FROM purchases_with_timestamp;
- PRODUCT_ID CUSTOMER_ID MADE_ON
- ---------- ----------- --------------------------
- 1 1 13-MAY-05 07.15.31.1234 AM
SELECT *
FROM purchases_with_timestamp;
PRODUCT_ID CUSTOMER_ID MADE_ON
---------- ----------- --------------------------
1 1 13-MAY-05 07.15.31.1234 AM
2. 使用TIMESTAMP WITH TIME ZONE类型
TIMESTAMP WITH TIME ZONE类型对TIMESTAMP进行了扩展，用于存储时区。下面这个语句创建一个表purchases_timestamp_with_tz，该表用于存储顾客的购买记录。该表包含了一个TIMESTAMP WITH TIME ZONE类型的列made_on，该列用来记录该顾客在何时购买了产品：
- CREATE TABLE purchases_timestamp_with_tz (
- product_id INTEGER REFERENCES products(product_id),
- customer_id INTEGER REFERENCES customers(customer_id),
- made_on TIMESTAMP(4) WITH TIME ZONE
- );
CREATE TABLE purchases_timestamp_with_tz (
product_id INTEGER REFERENCES products(product_id),
customer_id INTEGER REFERENCES customers(customer_id),
made_on TIMESTAMP(4) WITH TIME ZONE
);
要向数据库提供一个带有时区信息的时间戳，可以在TIMESTAMP子句中加上时区信息。例如，下面这个TIMESTAMP子句包含了时差-07:00：
- TIMESTAMP '2005-05-13 07:15:31.1234 -07:00'
- 此处也可以指定一个时区名，下面这个例子就指定PST作为时区使用：
- TIMESTAMP '2005-05-13 07:15:31.1234 PST'
- 下面这个INSERT语句向purchases_timestamp_with_tz表中添加两行记录：
- INSERT INTO purchases_timestamp_with_tz (
- product_id, customer_id, made_on
- ) VALUES (
- 1, 1, TIMESTAMP '2005-05-13 07:15:31.1234 -07:00'
- );
- INSERT INTO purchases_timestamp_with_tz (
- product_id, customer_id, made_on
- ) VALUES (
- 1, 2, TIMESTAMP '2005-05-13 07:15:31.1234 PST'
- );
TIMESTAMP '2005-05-13 07:15:31.1234 -07:00'
此处也可以指定一个时区名，下面这个例子就指定PST作为时区使用：
TIMESTAMP '2005-05-13 07:15:31.1234 PST'
下面这个INSERT语句向purchases_timestamp_with_tz表中添加两行记录：
INSERT INTO purchases_timestamp_with_tz (
product_id, customer_id, made_on
) VALUES (
1, 1, TIMESTAMP '2005-05-13 07:15:31.1234 -07:00'
);
INSERT INTO purchases_timestamp_with_tz (
product_id, customer_id, made_on
) VALUES (
1, 2, TIMESTAMP '2005-05-13 07:15:31.1234 PST'
);
下面这个查询对purchases_timestamp_with_tz表进行检索：
- SELECT *
- FROM purchases_timestamp_with_tz;
- PRODUCT_ID CUSTOMER_ID MADE_ON
- ---------- ----------- ---------------------------------
- 1 1 13-MAY-05 07.15.31.1234 AM -07:00
- 1 2 13-MAY-05 07.15.31.1234 AM PST
SELECT *
FROM purchases_timestamp_with_tz;
PRODUCT_ID CUSTOMER_ID MADE_ON
---------- ----------- ---------------------------------
1 1 13-MAY-05 07.15.31.1234 AM -07:00
1 2 13-MAY-05 07.15.31.1234 AM PST
3. 使用TIMESTAMP WITH LOCAL TIME ZONE类型
TIMESTAMP WITH LOCAL TIME ZONE类型对TIMESTAMP进行了扩展，用于存储一个转换为数据库设置的本地时区的时间戳。在为一个TIMESTAMP WITH LOCAL TIME ZONE类型的列存储一个时间戳值时，这个时间戳就被转换(或被规格化)为数据库的时区设置。当检索这个时间戳时，它就被规格化为当前会话中的时区设置。
提示：
如果企业已经实现了一个全球系统，可以通过世界各地访问，那么就应该使用TIMESTAMP WITH LOCAL TIME ZONE类型来存储时间戳。这是因为数据库会将时间戳存储为数据库所在时区中的本地时间，但是在对它们进行检索时，却可以看到规格化为本地时区的时间戳。
假设数据库时区是PST(比UTC晚8个小时)，想向数据库中存储下面的时间戳：
2005-05-13 07:15:30 EST
由于EST比UTC晚5个小时，因此EST与PST之间差3个小时(8 - 5 = 3)。要将前面的时间戳规格化为PST，就必须从时间戳中减去3个小时就得到下面的规格化的时间戳：
2005-05-13 04:15:30
这是数据库的TIMESTAMP WITH LOCAL TIME ZONE列中所存储的时间戳。
下面这个语句创建一个表purchases_ with_local_tz，该表用于存储顾客的购买记录。该表包含了一个TIMESTAMP WITH LOCAL TIME ZONE类型的列made_on，该列用来记录顾客在何时购买了产品：
- CREATE TABLE purchases_with_local_tz (
- product_id INTEGER REFERENCES products(product_id),
- customer_id INTEGER REFERENCES customers(customer_id),
- made_on TIMESTAMP(4) WITH LOCAL TIME ZONE
- );
CREATE TABLE purchases_with_local_tz (
product_id INTEGER REFERENCES products(product_id),
customer_id INTEGER REFERENCES customers(customer_id),
made_on TIMESTAMP(4) WITH LOCAL TIME ZONE
);下面这个INSERT语句向purchases_ with_local_tz表中添加一行记录，并将该行的made_on列值设置为2005-05-13 07:15:30 EST：
- INSERT INTO purchases_with_local_tz (
- product_id, customer_id, made_on
- ) VALUES (
- 1, 1, TIMESTAMP '2005-05-13 07:15:30 EST'
- );
INSERT INTO purchases_with_local_tz (
product_id, customer_id, made_on
) VALUES (
1, 1, TIMESTAMP '2005-05-13 07:15:30 EST'
);
虽然made_on列的时间戳设置为2005-05-13 07:15:30 EST，但数据库中实际存储的时间戳是2005-05-13 04:15:30(时间戳规格化为PST)。
下面这个查询对purchases_ with_local_tz表进行检索：
- SELECT *
- FROM purchases_with_local_tz;
- PRODUCT_ID CUSTOMER_ID MADE_ON
- ---------- ----------- --------------------------
- 1 1 13-MAY-05 04.15.30.0000 AM
SELECT *
FROM purchases_with_local_tz;
PRODUCT_ID CUSTOMER_ID MADE_ON
---------- ----------- --------------------------
1 1 13-MAY-05 04.15.30.0000 AM
因为我的数据库时区和会话时区都是PST，所以此查询返回的时间戳都是针对PST的。
警告：
前面这个查询返回的时间戳规格化为PST。如果数据库时区或会话时区不是PST，则运行查询时返回的时间戳会有所不同(它将规格化为您的时区)。
如果将当前会话的本地时区设置为EST，并重新执行上面这个查询，就会看到时间戳被规格化为EST：
- ALTER SESSION SET TIME_ZONE = 'EST';
- Session altered.
- SELECT *
- FROM purchases_with_local_tz;
- PRODUCT_ID CUSTOMER_ID MADE_ON
- ---------- ----------- --------------------------
- 1 1 13-MAY-05 07.15.30.0000 AM
ALTER SESSION SET TIME_ZONE = 'EST';
Session altered.
SELECT *
FROM purchases_with_local_tz;
PRODUCT_ID CUSTOMER_ID MADE_ON
---------- ----------- --------------------------
1 1 13-MAY-05 07.15.30.0000 AM
可以看到，此查询返回的时间戳是13-MAY-05 07.15.30.0000 AM，这是规格化为会话时区EST的时间戳。因为EST比PST早3小时，所以必须将13-MAY-05 04:15:30(数据库中存储的时间戳)增加3小时，得到13-MAY-05 07.15.30 AM(查询返回的时间戳)。
下面这条语句将会话时区设置回PST：
- ALTER SESSION SET TIME_ZONE = 'PST';
- ession altered.
ALTER SESSION SET TIME_ZONE = 'PST';
Session altered.有很多函数可以用来查询和处理时间戳，表5-9中列出了这些函数。
函 数 说 明 CURRENT_TIMESTAMP() 返回一个 TIMESTAMP WITH TIME ZONE 类型的值，其中包括当前会话的日期和时间以及会话的时区 EXTRACT(
{ YEAR | MONTH | DAY |
HOUR | MINUTE | SECOND } |
{ TIMEZONE_HOUR |
TIMEZONE_MINUTE } |
{ TIMEZONE_REGION | }
TIMEZONE_ABBR }
FROM x) 从x中提取并返回年、月、日、时、分、秒或时区，其中x可以是时间戳类型或DATE类型 FROM_TZ( x, time_zone) 将TIMESTAMP类型的x转换为由time_zone指定的时区，并返回TIMESTAMP WITH TIMEZONE类型。time_zone必须被指定为+|- HH:MI格式的字符串。此函数一般将x和time_zone合并成一个值 LOCALTIMESTAMP 返回一个TIMESTAMP 类型，其中包含会话的当前日期和时间 SYSTIMESTAMP 返回一个TIMESTAMP WITH TIME ZONE类型，其中包括数据库的当前日期、时间，以及数据库时区 SYS_EXTRACT_UTC(x) 将TIMESTAMP WITH TIMEZONE类型的x转换为一个TIMESTAMP类型，其中包含了UTC时区中的日期和时间 TO_TIMESTAMP(x, [format]) 将字符串x转换为一个TIMESTAMP类型，还可以为x指定一个可选的参数format TO_TIMESTAMP_TZ(x, [format]) 将字符串x转换为一个TIMESTAMP WITH TIMEZONE类型，还可以为x指定一个可选的参数format
1. CURRENT_TIMESTAMP、LOCALTIMESTAMP和SYSTIMESTAMP
下面这个查询同时调用了CURRENT_TIMESTAMP、LOCALTIMESTAMP和SYSTIMESTAMP函数(我的会话时区和数据库时区都是PST，它比UTC晚8小时)：
Sql代码
- SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP
- FROM dual;
- CURRENT_TIMESTAMP
- -----------------------------------
- LOCALTIMESTAMP
- -----------------------------------
- SYSTIMESTAMP
- -----------------------------------
- 05-NOV-07 12.15.32.734000 PM PST
- 05-NOV-07 12.15.32.734000 PM
- 05-NOV-07 12.15.32.734000 PM -08:00
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP
FROM dual;
CURRENT_TIMESTAMP
-----------------------------------
LOCALTIMESTAMP
-----------------------------------
SYSTIMESTAMP
-----------------------------------
05-NOV-07 12.15.32.734000 PM PST
05-NOV-07 12.15.32.734000 PM
05-NOV-07 12.15.32.734000 PM -08:00
如果将TIME_ZONE设置为EST，并重新执行上面这个查询，就会得到如下输出结果：
Sql代码
- ALTER SESSION SET TIME_ZONE = 'EST';
- Session altered.
- SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP
- FROM dual;
- CURRENT_TIMESTAMP
- -----------------------------------------------------------
- LOCALTIMESTAMP
- -----------------------------------------------------------
- SYSTIMESTAMP
- -----------------------------------------------------------
- 05-NOV-07 03.19.57.562000 PM EST
- 05-NOV-07 03.19.57.562000 PM
- 05-NOV-07 12.19.57.562000 PM -08:00
ALTER SESSION SET TIME_ZONE = 'EST';
Session altered.
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP
FROM dual;
CURRENT_TIMESTAMP
-----------------------------------------------------------
LOCALTIMESTAMP
-----------------------------------------------------------
SYSTIMESTAMP
-----------------------------------------------------------
05-NOV-07 03.19.57.562000 PM EST
05-NOV-07 03.19.57.562000 PM
05-NOV-07 12.19.57.562000 PM -08:00
下面这条语句将会话时区设置会PST：
Sql代码
- ALTER SESSION SET TIME_ZONE = 'PST';
- Session altered.
ALTER SESSION SET TIME_ZONE = 'PST';
Session altered.
2. EXTRACT函数
EXTRACT函数用于从x中提取并返回年、月、日、时、分、秒或时区，其中x可以是时间戳类型或DATE类型。下面这个查询使用EXTRACT函数从由TO_DATE()返回的DATE类型中提取年、月、日：
Sql代码
- SELECT
- EXTRACT(YEAR FROM TO_DATE('01-JAN-2008 19:15:26',
- 'DD-MON-YYYY HH24:MI:SS')) AS YEAR,
- EXTRACT(MONTH FROM TO_DATE('01-JAN-2008 19:15:26',
- 'DD-MON-YYYY HH24:MI:SS')) AS MONTH,
- EXTRACT(DAY FROM TO_DATE('01-JAN-2008 19:15:26',
- 'DD-MON-YYYY HH24:MI:SS')) AS DAY
- FROM dual;
- YEAR MONTH DAY
- ---------- ---------- ----------
- 2008 1 1
SELECT
EXTRACT(YEAR FROM TO_DATE('01-JAN-2008 19:15:26',
'DD-MON-YYYY HH24:MI:SS')) AS YEAR,
EXTRACT(MONTH FROM TO_DATE('01-JAN-2008 19:15:26',
'DD-MON-YYYY HH24:MI:SS')) AS MONTH,
EXTRACT(DAY FROM TO_DATE('01-JAN-2008 19:15:26',
'DD-MON-YYYY HH24:MI:SS')) AS DAY
FROM dual;
YEAR MONTH DAY
---------- ---------- ----------
2008 1 1
下面这个查询使用EXTRACT函数从由TO_TIMESTAMP()返回的TIMESTAMP类型中提取时、分、秒：
Sql代码
- SELECT
- EXTRACT(HOUR FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',
- 'DD-MON-YYYY HH24:MI:SS')) AS HOUR,
- EXTRACT(MINUTE FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',
- 'DD-MON-YYYY HH24:MI:SS')) AS MINUTE,
- EXTRACT(SECOND FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',
- 'DD-MON-YYYY HH24:MI:SS')) AS SECOND
- FROM dual;
- HOUR MINUTE SECOND
- ---------- ---------- ----------
- 19 15 26
SELECT
EXTRACT(HOUR FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',
'DD-MON-YYYY HH24:MI:SS')) AS HOUR,
EXTRACT(MINUTE FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',
'DD-MON-YYYY HH24:MI:SS')) AS MINUTE,
EXTRACT(SECOND FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',
'DD-MON-YYYY HH24:MI:SS')) AS SECOND
FROM dual;
HOUR MINUTE SECOND
---------- ---------- ----------
19 15 26
最后这个查询使用EXTRACT函数从由TO_TIMESTAMP_TZ()返回的TIMESTAMP WITH TIMEZONE类型中提取时区的时、分、秒、区域和时区缩写：
Sql代码
- SELECT
- EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ(
- '01-JAN-2008 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'))
- AS TZH,
- EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ(
- '01-JAN-2008 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'))
- AS TZM,
- EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ(
- '01-JAN-2008 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))
- AS TZR,
- EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ(
- '01-JAN-2008 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))
- AS TZA
- FROM dual;
- TZH TZM TZR TZA
- ---------- ---------- ----------- ----------
- -7 -15 PST PST
SELECT
EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ(
'01-JAN-2008 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'))
AS TZH,
EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ(
'01-JAN-2008 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'))
AS TZM,
EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ(
'01-JAN-2008 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))
AS TZR,
EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ(
'01-JAN-2008 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))
AS TZA
FROM dual;
TZH TZM TZR TZA
---------- ---------- ----------- ----------
-7 -15 PST PST
3. FROM_TZ()
FROM_TZ(x, time_zone)将TIMESTAMP类型的x转换为由time_zone指定的时区，并返回TIMESTAMP WITH TIMEZONE类型。time_zone必须被指定为+|- HH:MI格式的字符串。此函数一般将x和time_zone合并成一个值。
例如，下面这个查询将时间戳2008-05-13 07:15:31.1234和与UTC的时差-7:00合并起来：
Sql代码
- SELECT FROM_TZ(TIMESTAMP '2008-05-13 07:15:31.1234', '-7:00')
- FROM dual;
- FROM_TZ(TIMESTAMP'2008-05-1307:15:31.1234','-7:00')
- ---------------------------------------------------
- 13-MAY-08 07.15.31.123400000 AM -07:00
SELECT FROM_TZ(TIMESTAMP '2008-05-13 07:15:31.1234', '-7:00')
FROM dual;
FROM_TZ(TIMESTAMP'2008-05-1307:15:31.1234','-7:00')
---------------------------------------------------
13-MAY-08 07.15.31.123400000 AM -07:00
4. SYS_EXTRACT_UTC()
SYS_EXTRACT_UTC (x)用于将TIMESTAMP WITH TIMEZONE类型的x转换为一个包含UTC时区日期和时间的TIMESTAMP类型。
下面这个查询将2008-11-17 19:15:26 PST转换为UTC：
Sql代码
- SELECT SYS_EXTRACT_UTC(TIMESTAMP '2008-11-17 19:15:26 PST')
- FROM dual;
- SYS_EXTRACT_UTC(TIMESTAMP'2008-11-1719:15:26PST')
- -------------------------------------------------
- 18-NOV-08 03.15.26.000000000 AM
SELECT SYS_EXTRACT_UTC(TIMESTAMP '2008-11-17 19:15:26 PST')
FROM dual;
SYS_EXTRACT_UTC(TIMESTAMP'2008-11-1719:15:26PST')
-------------------------------------------------
18-NOV-08 03.15.26.000000000 AM
因为在冬季PST比UTC晚8个小时，因此这个查询返回的TIMESTAMP类型的值比2008-11-17 19:15:26 PST早了8个小时，即18-NOV-08 03.15.26 AM.。
如果是在夏季，则返回的TIMESTAMP类型的值只比UTC早7个小时。
Sql代码
- SELECT SYS_EXTRACT_UTC(TIMESTAMP '2008-05-17 19:15:26 PST')
- FROM dual;
- SYS_EXTRACT_UTC(TIMESTAMP'2008-05-1719:15:26PST')
- -------------------------------------------------
- 18-MAY-08 02.15.26.000000000 AM
SELECT SYS_EXTRACT_UTC(TIMESTAMP '2008-05-17 19:15:26 PST')
FROM dual;
SYS_EXTRACT_UTC(TIMESTAMP'2008-05-1719:15:26PST')
-------------------------------------------------
18-MAY-08 02.15.26.000000000 AM
5. TO_TIMESTAMP()
TO_TIMESTAMP(x,[format])用于将字符串x(可能是CHAR、VARCHAR2、NCHAR或NVARCHAR2)转换为一个TIMESTAMP类型，该函数中可以为x指定可选的参数format。
下面这个查询将字符串2005-05-13 07:15:31.1234转换为TIMESTAMP类型，格式为YYYY-MM-DD HH24:MI:SS.FF：
Sql代码
- SELECT TO_TIMESTAMP('2008-05-13 07:15:31.1234', 'YYYY-MM-DD HH24:MI:SS.FF')
- FROM dual;
- TO_TIMESTAMP('2008-05-1307:15:31.1234','YYYY-MM-DDHH24:MI:SS.FF')
- -----------------------------------------------------------------
- 13-MAY-08 07.15.31.123400000 AM
SELECT TO_TIMESTAMP('2008-05-13 07:15:31.1234', 'YYYY-MM-DD HH24:MI:SS.FF')
FROM dual;
TO_TIMESTAMP('2008-05-1307:15:31.1234','YYYY-MM-DDHH24:MI:SS.FF')
-----------------------------------------------------------------
13-MAY-08 07.15.31.123400000 AM
6. TO_TIMESTAMP_TZ()
TO_TIMESTAMP_TZ(x,[ format ])用于将字符串x转换为一个TIMESTAMP WITH TIMEZONE类型，该函数中可以为x指定可选的参数format。
下面这个查询向TO_TIMESTAMP_TZ函数传递时区PST(在格式字符串中使用TZR来识别)：
Sql代码
- SELECT TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 PST',
- 'YYYY-MM-DD HH24:MI:SS.FF TZR')
- FROM dual;
- TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234PST','YYYY-MM-DDHH24:MI:SS.FFTZR')
- --------------------------------------------------------------------------
- 13-MAY-08 07.15.31.123400000 AM PST
SELECT TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 PST',
'YYYY-MM-DD HH24:MI:SS.FF TZR')
FROM dual;
TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234PST','YYYY-MM-DDHH24:MI:SS.FFTZR')
--------------------------------------------------------------------------
13-MAY-08 07.15.31.123400000 AM PST
下面这个查询使用与UTC的时差 -7:00(-7:00在格式字符串中用TZR和TZM来识别)：
Sql代码
- SELECT TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 -7:00',
- 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')
- FROM dual;
- TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234-7:00','YYYY-MM-DDHH24:MI:SS.FFTZH
- --------------------------------------------------------------------------
- 13-MAY-08 07.15.31.123400000 AM -07:00
SELECT TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 -7:00',
'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')
FROM dual;
TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234-7:00','YYYY-MM-DDHH24:MI:SS.FFTZH
--------------------------------------------------------------------------
13-MAY-08 07.15.31.123400000 AM -07:00
7. 将字符串转换为TIMESTAMP WITH LOCAL TIME ZONE
使用CAST函数可以将一个字符串转换为TIMESTAMP WITH LOCAL TIME ZONE。第4章曾介绍过CAST()，回想一下，CAST(x AS type)将x转换为由type指定的兼容的数据库类型。
下面这个查询使用CAST()将字符串13-JUN-08转换为TIMESTAMP WITH LOCAL TIME ZONE：
Sql代码
- SELECT CAST('13-JUN-08' AS TIMESTAMP WITH LOCAL TIME ZONE)
- FROM dual;
- CAST('13-JUN-08'ASTIMESTAMPWITHLOCALTIMEZONE)
- ---------------------------------------------
- 13-JUN-08 12.00.00.000000 AM
SELECT CAST('13-JUN-08' AS TIMESTAMP WITH LOCAL TIME ZONE)
FROM dual;
CAST('13-JUN-08'ASTIMESTAMPWITHLOCALTIMEZONE)
---------------------------------------------
13-JUN-08 12.00.00.000000 AM
此查询返回的时间戳包含日期2008年6月13日和时间12 A.M.。
下面这个查询使用CAST()将一个更复杂的字符串转换为TIMESTAMP WITH LOCAL TIME ZONE：
Sql代码
- SELECT CAST(TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 PST',
- 'YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE)
- FROM dual;
- CAST(TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234PST','YYYY-MM-DDHH24:MI:SS.FF
- --------------------------------------------------------------------------
- 13-MAY-08 06.15.31.123400 AM
SELECT CAST(TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 PST',
'YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE)
FROM dual;
CAST(TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234PST','YYYY-MM-DDHH24:MI:SS.FF
--------------------------------------------------------------------------
13-MAY-08 06.15.31.123400 AM
此查询返回的时间戳包含日期2008年5月13日和时间6:15:31.1234 AM PST(PST是数据库时区和会话时区)。
下面这个查询与上面的查询做相同的事情，只是时区是EST：
Sql代码
- SELECT CAST(TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 EST',
- 'YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE)
- FROM dual;
- CAST(TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234EST','YYYY-MM-DDHH24:MI:SS.FF
- --------------------------------------------------------------------------
- 13-MAY-08 04.15.31.123400 AM
SELECT CAST(TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 EST',
'YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE)
FROM dual;
CAST(TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234EST','YYYY-MM-DDHH24:MI:SS.FF
--------------------------------------------------------------------------
13-MAY-08 04.15.31.123400 AM
此查询返回的时区包含日期2008年3月13日和时间4:15:31.1234 AM PST(因为PST比EST晚3小时，所以此时间戳返回的时间比实际查询的时间早3小时)。
SQL之时区、时间戳相关的函数
一、时区相关概念（数据库）
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
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
4483
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
