Oracle与时间戳有关的函数

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ZXCVBNM645/article/details/83724097

有很多函数可以用来查询和处理时间戳,表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

 

如果将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

 

下面这条语句将会话时区设置会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
 



    下面这个查询使用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
 



    最后这个查询使用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
 



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
 



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
 



    因为在冬季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

 

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
 



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
 



    下面这个查询使用与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
 


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
 



    此查询返回的时间戳包含日期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
 



    此查询返回的时间戳包含日期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
 



    此查询返回的时区包含日期2008年3月13日和时间4:15:31.1234 AM PST(因为PST比EST晚3小时,所以此时间戳返回的时间比实际查询的时间早3小时)。

 

 

 

 

 

 

 

 

 

 

 

展开阅读全文

有关ORACLE OCI函数问题

03-18

大家好,小弟在新公司改一个程序,这个程序用到了OCI函数,其中有一段代码如下rn#ifdef ORACLE8rn status = OCIStmtExecute(svchp,stmthp,errhp,(ub4)0, (ub4)0,(CONST OCISnapshot *)rn NULL,(OCISnapshot *)NULL,(ub4)OCI_SCROLLABLE_CURSOR);rn#endifrn#ifdef ORACLE9rnstatus = OCIStmtExecute(svchp,stmthp,errhp,(ub4)0, (ub4)0,(CONST OCISnapshot *)rn NULL,(OCISnapshot *)NULL,(ub4)OCI_STMT_SCROLLABLE_READONLY);rn#endifrn这个程序支持ORACLE8和ORACLE9,头文件的宏定义 #define ORACLE9 0rn这个程序编译出现以下问题,用的是makefile,操作系统位AIX5.1,数据库为ORACLE9rn /usr/vacpp/bin/xlc -c ../public/public.c -o ../public/public.orn test -d ./bin||mkdir binrn /usr/vacpp/bin/xlc -c -o src/extract.o -I /oracle/rdbms/demo -I /oracle/rdbms/public -I /oracle/plsql/public -I /oracle/network/public -I /oracle/precomp/public src/extract.c rn"src/extract.c", line 971.95: 1506-045 (S) Undeclared identifier OCI_STMT_SCROLLABLE_READONLY.rnmake: 1254-004 The error code from the last command is 1.rn我把定义改成#define ORACLE8 0rn编译,又出现如下问题,把status = OCIStmtExecute(svchp,stmthp,errhp,(ub4)0, (ub4)0,(CONST OCISnapshot *)rn NULL,(OCISnapshot *)NULL,(ub4)OCI_STMT_SCROLLABLE_READONLY);中最好一个参数OCI_STMT_SCROLLABLE_READONLY改成其他,如OCI_DEFAULT,也会出现以下编译问题:rn /usr/vacpp/bin/xlc -c ../public/public.c -o ../public/public.orn test -d ./bin||mkdir binrn /usr/vacpp/bin/xlc -c -o src/extract.o -I /oracle/rdbms/demo -I /oracle/rdbms/public -I /oracle/plsql/public -I /oracle/network/public -I /oracle/precomp/public src/extract.c rn /usr/vacpp/bin/xlc -o bin/e_extract -L/oracle/lib32/ -L/oracle/rdbms/lib32/ -R/disk0/o9i/app/products/oracle/9.0.2/lib32 -laio -lposix4 -lkstat -lm -lthread -lclntsh `cat /oracle/lib/ldflags` `cat /oracle/lib/sysliblist` src/extract.o ../public/public.ornld: 0706-027 The -R /disk0/o9i/app/products/oracle/9.0.2/lib32 flag is ignored.rnld: 0706-006 Cannot find or open library file: -l aiorn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l posix4rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l kstatrn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l threadrn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l clntshrn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l nbeq8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l nhost8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l n8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ncrypt8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l nidx8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l n8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ncrypt8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l nus8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l n8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ncrypt8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l nk58rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l n8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ncrypt8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l nldap8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ldapclnt8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l nsslb8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l n8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ncrypt8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l n8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ncrypt8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l noname8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l n8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ncrypt8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l nrad8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l n8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ncrypt8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l n8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ncrypt8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l nsid8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l n8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ncrypt8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ntcp8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ntcps8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l nsslb8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l nent8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ntcp8rn ld:open(): A file or directory in the path name does not exist.rnld: 0706-006 Cannot find or open library file: -l ntns8rn ld:open(): A file or directory in the path name does not exist.rnmake: 1254-004 The error code from the last command is 255.rnrn请问这是什么问题?如何解决?谢谢rn 论坛

没有更多推荐了,返回首页