Oracle 日期类型timestamp(时间戳)和date类型使用 - hijushenOracle 日期类型timestamp(时间戳)和date类型使用

时间:2024-02-22 18:18:36
Oracle 日期类型timestamp(时间戳)和date类型使用
1、获取系统时间的语句(ssxff6获取小数点后面六位) 
select sysdate,systimestamp,to_char(systimestamp, \'yyyymmdd hh24:mi:ssxff6\'), 
  to_char(systimestamp, \'yyyymmdd hh24:mi:ss.ff6\') from dual;  

2、字符型转成timestamp 
select to_timestamp(\'2011-09-14 12:52:42.123456789\', \'syyyy-mm-dd hh24:mi:ss.ff\') from dual; 

3、timestamp转成date型 
select cast(to_timestamp(\'2011-09-14 12:52:42.123456789\', \'syyyy-mm-dd hh24:mi:ss.ff\') as date) timestamp_to_date from dual;  

4、date型转成timestamp 
select cast(sysdate as timestamp) date_to_timestamp from dual;  

5、两date的日期相减得出的是天数,而两timestamp的日期相减得出的是完整的年月日时分秒小数秒 
select sysdate-sysdate,systimestamp-systimestamp from dual; 

select extract(day from inter) * 24 * 60 * 60 +   
  extract(hour from inter) * 60 * 60 + extract(minute from inter) * 60 +   
    extract(second from inter) "seconds" from 

  select to_timestamp(\'2011-09-14 12:34:23.281000000\', \'yyyy-mm-dd hh24:mi:ss.ff\') -   
    to_timestamp(\'2011-09-14 12:34:22.984000000\', \'yyyy-mm-dd hh24:mi:ss.ff\') inter from dual 
); 

select extract(second from to_timestamp(\'2011-09-14 12:34:23.281000000\', \'yyyy-mm-dd hh24:mi:ss.ff\'))- 
  extract(second from to_timestamp(\'2011-09-14 12:34:22.984000000\', \'yyyy-mm-dd hh24:mi:ss.ff\')) from dual; 

注:所以,timestamp要算出两日期间隔了多少秒,要用函数转换一下。 
to_char函数支持date和timestamp,但是trunc却不支持TIMESTAMP数据类型。 

DATE数据类型

可以存储月,年,日,世纪,时,分和秒。度量粒度是秒

以使用TO_CHAR函数把DATE数据进行传统地包装,达到表示成多种格式的目的

SELECT TO_CHAR(sysdate,\'MM/DD/YYYY HH24:MI:SS\') "Date" FROM dual

大多数人陷入的麻烦就是计算两个时间间的间隔年数、月数、天数、小时数和秒数。你需要明白的是,当你进行两个日期的相减运算的时候,得到的是“带小数位的天数”。

可以通过一个函数将天数转换成“天时分秒”格式:

CREATE OR REPLACE FUNCTION F_DAYS2STR(P_DAYS IN NUMBER DEFAULT 0)
    RETURN VARCHAR2 IS
    
--Ver:1.0
    
--Created by xsb on 2005-05-26
    
--For: 将天数转换成天时分秒格式
    DAYS   NUMBER := NVL(P_DAYS, 
0);
    VD     NUMBER; 
--
    VH     NUMBER; 
--小时
    VM     NUMBER; 
--
    VS     NUMBER; 
--
    RESULT VARCHAR2(
100); --返回值
BEGIN
    VD := TRUNC(DAYS);
    VH := TRUNC((DAYS - VD) * 
24);
    VM := TRUNC((DAYS - VD - VH / 
24) * 24 * 60);
    VS := TRUNC((DAYS - VD - VH / 
24 - VM / 24 / 60) * 24 * 60 * 60);
    SELECT DECODE(VD, 
0\'\', VD || \'\') || DECODE(VH, 0\'\', VH || \'小时\') ||DECODE(VM, 0\'\', VM || \'\') || DECODE(VS, 0\'\', VS ||\'\')   INTO RESULT FROM DUAL;
    RETURN(RESULT);
END;

TIMESTAMP 数据类型

它包括了所有DATE数据类型的年月日时分秒的信息,而且包括了小数秒的信息。

SELECT sysdate,systimestamp FROM dual

注意,to_char函数支持datetimestamp,但是trunc却不支持TIMESTAMP数据类型。

SELECT sysdate,systimestamp,to_char(systimestamp,\'yyyymmdd hh24:mi:ssxff3\') FROM dual

当你直接相减的话,看看会发生什么。结果将更容易理解,

select systimestamp-systimestamp from dual

这就意味着不用再通过麻烦的计算求出多少天多少秒,用substr函数摘取出其中的数字即可!

结论:当使用datetimestamp类型的时候,选择是很清楚的。你可以随意处置datetimestamp类型。当你的时间粒度必须精确至秒以下时,可以考虑使用timestamp

再来看如何实现:将天数转换成“天时分秒”格式:

使用DATE数据类型:

create table t2 (d1 date,d2 date) ;
INSERT INTO T2 VALUES (SYSDATESYSDATE + 
1.1234);
SELECT D1, D2, D2 - D1, F_DAYS2STR(D2 - D1),
            CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP)
    FROM T2


SELECT TO_NUMBER(SUBSTR(D21, 
1, INSTR(D21, \' \'))) D,
            SUBSTR(D21, INSTR(D21, 
\' \') + 12) H,
            SUBSTR(D21, INSTR(D21, 
\' \') + 42) M,
            SUBSTR(D21, INSTR(D21, 
\' \') + 72) S
    FROM (SELECT CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) D21
           FROM T2)
DATE数据类型

可以存储月,年,日,世纪,时,分和秒。度量粒度是秒

以使用TO_CHAR函数把DATE数据进行传统地包装,达到表示成多种格式的目的

SELECT TO_CHAR(sysdate,\'MM/DD/YYYY HH24:MI:SS\') "Date" FROM dual

大多数人陷入的麻烦就是计算两个时间间的间隔年数、月数、天数、小时数和秒数。你需要明白的是,当你进行两个日期的相减运算的时候,得到的是“带小数位的天数”。

可以通过一个函数将天数转换成“天时分秒”格式:

CREATE OR REPLACE FUNCTION F_DAYS2STR(P_DAYS IN NUMBER DEFAULT 0)
    RETURN VARCHAR2 IS
    
--Ver:1.0
    
--Created by xsb on 2005-05-26
    
--For: 将天数转换成天时分秒格式
    DAYS   NUMBER := NVL(P_DAYS, 
0);
    VD     NUMBER; 
--
    VH     NUMBER; 
--小时
    VM     NUMBER; 
--
    VS     NUMBER; 
--
    RESULT VARCHAR2(
100); --返回值
BEGIN
    VD := TRUNC(DAYS);
    VH := TRUNC((DAYS - VD) * 
24);
    VM := TRUNC((DAYS - VD - VH / 
24) * 24 * 60);
    VS := TRUNC((DAYS - VD - VH / 
24 - VM / 24 / 60) * 24 * 60 * 60);
    SELECT DECODE(VD, 
0\'\', VD || \'\') || DECODE(VH, 0\'\', VH || \'小时\') ||DECODE(VM, 0\'\', VM || \'\') || DECODE(VS, 0\'\', VS ||\'\')   INTO RESULT FROM DUAL;
    RETURN(RESULT);
END;

TIMESTAMP 数据类型

它包括了所有DATE数据类型的年月日时分秒的信息,而且包括了小数秒的信息。

SELECT sysdate,systimestamp FROM dual

注意,to_char函数支持datetimestamp,但是trunc却不支持TIMESTAMP数据类型。

SELECT sysdate,systimestamp,to_char(systimestamp,\'yyyymmdd hh24:mi:ssxff3\') FROM dual

当你直接相减的话,看看会发生什么。结果将更容易理解,

select systimestamp-systimestamp from dual

这就意味着不用再通过麻烦的计算求出多少天多少秒,用substr函数摘取出其中的数字即可!

结论:当使用datetimestamp类型的时候,选择是很清楚的。你可以随意处置datetimestamp类型。当你的时间粒度必须精确至秒以下时,可以考虑使用timestamp

再来看如何实现:将天数转换成“天时分秒”格式:

使用DATE数据类型:

create table t2 (d1 date,d2 date) ;
INSERT INTO T2 VALUES (SYSDATESYSDATE + 
1.1234);
SELECT D1, D2, D2 - D1, F_DAYS2STR(D2 - D1),
            CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP)
    FROM T2


SELECT TO_NUMBER(SUBSTR(D21, 
1, INSTR(D21, \' \'))) D,
            SUBSTR(D21, INSTR(D21, 
\' \') + 12) H,
            SUBSTR(D21, INSTR(D21, 
\' \') + 42) M,
            SUBSTR(D21, INSTR(D21, 
\' \') + 72) S
    FROM (SELECT CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) D21
           FROM T2)