Oracle:计算2个日期之间的HH:MM:SS时差 [英] Oracle: Calculate time difference in HH:MM:SS between 2 dates

问题描述

我有2个日期，格式如下:

I have 2 dates with following format:

ST_DT = 2013年12月29日星期日，美国东部时间

ST_DT = Sun Dec 29 11:55:29 EST 2013

ED_DT = 2013年12月30日星期二20:21:34

ED_DT = Tue Dec 30 20:21:34 EST 2013

我想找到HH:MM:SS格式的这两个日期之间的差异.现在我的问题是我不知道如何在Oracle中解析上述日期格式.

I want to find the difference between these 2 dates in HH:MM:SS format. Now my problem is that i don't know how to parse the above date format in Oracle.

推荐答案

varchar2中的日期是否为类型?然后，您可以先将其转换为时间戳格式.由于还具有时区，因此请使用 to_timestamp_tz 功能.

Are the dates in varchar2 type? Then, you can first convert it into timestamp format. Since it has timezone also, use the to_timestamp_tz function.

SQL> select to_timestamp_tz('Sun Dec 29 11:55:29 EST 2013','Dy Mon dd hh24:mi:ss TZR yyyy') from dual;

TO_TIMESTAMP_TZ('SUNDEC2911:55:29EST2013','DYMONDDHH24:MI:SSTZRYYYY')
---------------------------------------------------------------------------
29-DEC-13 11.55.29.000000000 AM EST

日期为时间戳类型后，将其减去将为您带来间隔天到秒类型.

Once the dates are in timestamp type, subtracting them will give you the difference in interval day to second type.

SQL> select   to_timestamp_tz ('Mon Dec 30 20:21:34 EST 2013','Dy Mon dd hh24:mi:ss TZR yyyy')
  2         - to_timestamp_tz ('Sun Dec 29 11:55:29 EST 2013','Dy Mon dd hh24:mi:ss TZR yyyy') from dual;

TO_TIMESTAMP_TZ('MONDEC3020:21:34EST2013','DYMONDDHH24:MI:SSTZRYYYY')-TO_TI
---------------------------------------------------------------------------
+000000001 08:26:05.000000000

然后使用提取来获取间隔中的各个组成部分.

Then use extract to get the individual components from the interval.

SQL> select extract(day from intrvl) as dd,
  2         extract(hour from intrvl) as hh24,
  3         extract(minute from intrvl) as mi,
  4         extract(second from intrvl) as ss
  5  from (
  6        select   to_timestamp_tz ('Mon Dec 30 20:21:34 EST 2013','Dy Mon dd hh24:mi:ss TZR yyyy')
  7               - to_timestamp_tz ('Sun Dec 29 11:55:29 EST 2013','Dy Mon dd hh24:mi:ss TZR yyyy') as intrvl
  8       from dual
  9       );

        DD       HH24         MI         SS
---------- ---------- ---------- ----------
         1          8         26          5

这篇关于Oracle:计算2个日期之间的HH:MM:SS时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！