Oracle时间戳
● 时间戳可以存储秒的小数位。
● 时间戳可以存储时区。
下面介绍时间戳类型。
使用时间戳类型
时间戳有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小时):
- 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,并重新执行上面这个查询,就会得到如下输出结果:
- 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:
ALTER SESSION SET TIME_ZONE = 'PST'; Session altered.
2. EXTRACT函数
EXTRACT函数用于从x中提取并返回年、月、日、时、分、秒或时区,其中x可以是时间戳类型或DATE类型。下面这个查询使用EXTRACT函数从由TO_DATE()返回的DATE类型中提取年、月、日:
- 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类型中提取时、分、秒:
- 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类型中提取时区的时、分、秒、区域和时区缩写:
- 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合并起来:
- 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:
- 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个小时。
- 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:
- 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来识别):
- 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来识别):
- 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:
- 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:
- 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:
- 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小时)。
TAG:
标题搜索
日历
|
|||||||||
日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
1 | 2 | 3 | 4 | ||||||
5 | 6 | 7 | 8 | 9 | 10 | 11 | |||
12 | 13 | 14 | 15 | 16 | 17 | 18 | |||
19 | 20 | 21 | 22 | 23 | 24 | 25 | |||
26 | 27 | 28 | 29 | 30 | 31 |
我的存档
数据统计
- 访问量: 18317
- 日志数: 29
- 文件数: 2
- 建立时间: 2007-08-29
- 更新时间: 2010-12-28