The sample 'with' data in the examples below uses the TO_DATE function without a format string. To make the examples run, set your session's "NLS_date_format" parameter as follows (execute this command in your connected session):
Execute This:
ALTER SESSION set nls_date_format = 'YYYY-MM-DD HH24:MI:SS' ;
In this first example, note that we use the CAST function to change the date values to timestamp values. Subtracting two timestamp values gives us an interval. The EXTRACT function will then pull out the desired elements as shown in the result. So if your source data type is timestamp instead of date, then all you need to do is subtract and extract.
Also note that if you need to deal with dates that are out of chronological order, then your code will have to be a bit more precise. In the following examples this sign is calculated and dealt with in various ways.
/*===== code =====*/
ALTER SESSION set nls_date_format = 'YYYY-MM-DD HH24:MI:SS' ;
with
w_date_ranges as
( select to_date('1964-05-16 23:48:56') dt1, to_date('1964-05-17 10:23:18') dt2 from dual union all
select to_date('1985-08-28 22:04:22') , to_date('1985-08-28 19:15:07') from dual union all
select to_date('1991-03-11 02:05:44') , to_date('1991-03-11 01:49:38') from dual union all
select to_date('1998-03-07 06:13:18') , to_date('1998-03-08 06:13:17') from dual union all
select to_date('2001-12-14 01:43:13') , to_date('2001-12-13 01:43:13') from dual union all
select to_date('2003-11-29 18:53:10') , to_date('2003-11-30 18:53:11') from dual union all
select to_date('2009-06-10 10:11:48') , to_date('2009-06-24 09:23:55') from dual union all
select to_date('2009-10-25 00:00:00') , to_date('2009-10-05 18:47:23') from dual union all
select to_date('2010-01-01 13:27:03') , to_date('2012-04-13 01:00:27') from dual
)
select dt1
, dt2
, sign(dt2-dt1) as sign
, abs( extract( day from cast(dt2 as timestamp) - cast(dt1 as timestamp)) ) as ddd
, abs( extract( hour from cast(dt2 as timestamp) - cast(dt1 as timestamp)) ) as hh
, abs( extract( minute from cast(dt2 as timestamp) - cast(dt1 as timestamp)) ) as mm
, abs( extract( second from cast(dt2 as timestamp) - cast(dt1 as timestamp)) ) as ss
from w_date_ranges
order by 1 ;
/*===== Results =====*/
DT1 DT2 SIGN DDD HH MM SS
------------------- ------------------- ---- --- -- -- --
1964-05-16 23:48:56 1964-05-17 10:23:18 1 0 10 34 22
1985-08-28 22:04:22 1985-08-28 19:15:07 -1 0 2 49 15
1991-03-11 02:05:44 1991-03-11 01:49:38 -1 0 0 16 6
1998-03-07 06:13:18 1998-03-08 06:13:17 1 0 23 59 59
2001-12-14 01:43:13 2001-12-13 01:43:13 -1 1 0 0 0
2003-11-29 18:53:10 2003-11-30 18:53:11 1 1 0 0 1
2009-06-10 10:11:48 2009-06-24 09:23:55 1 13 23 12 7
2009-10-25 00:00:00 2009-10-05 18:47:23 -1 19 5 12 37
2010-01-01 13:27:03 2012-04-13 01:00:27 1 832 11 33 24
In this example the number of days is found by subtracting the two dates and truncating the decimal value. The TO_CHAR function is used to find the other elements. The difference between dates is added to the system date truncated to midnight. Then looking at the HH24, MI, and SS elements of the date with the TO_CHAR function gives us the results we found in the previous query.
/*===== code =====*/
ALTER SESSION set nls_date_format = 'YYYY-MM-DD HH24:MI:SS' ;
with
w_date_ranges as
( select to_date('1964-05-16 23:48:56') dt1, to_date('1964-05-17 10:23:18') dt2 from dual union all
select to_date('1985-08-28 22:04:22') , to_date('1985-08-28 19:15:07') from dual union all
select to_date('1991-03-11 02:05:44') , to_date('1991-03-11 01:49:38') from dual union all
select to_date('1998-03-07 06:13:18') , to_date('1998-03-08 06:13:17') from dual union all
select to_date('2001-12-14 01:43:13') , to_date('2001-12-13 01:43:13') from dual union all
select to_date('2003-11-29 18:53:10') , to_date('2003-11-30 18:53:11') from dual union all
select to_date('2009-06-10 10:11:48') , to_date('2009-06-24 09:23:55') from dual union all
select to_date('2009-10-25 00:00:00') , to_date('2009-10-05 18:47:23') from dual union all
select to_date('2010-01-01 13:27:03') , to_date('2012-04-13 01:00:27') from dual
)
select dt1
, dt2
, decode(sign(dt2-dt1),-1,'-','+') as sign
, trunc(abs(dt2-dt1)) as ddd
, to_number(to_char( trunc(sysdate)+abs(dt2-dt1), 'HH24' )) as hh
, to_number(to_char( trunc(sysdate)+abs(dt2-dt1), 'MI' )) as mm
, to_number(to_char( trunc(sysdate)+abs(dt2-dt1), 'SS' )) as ss
, to_char( trunc(sysdate)+abs(dt2-dt1), 'HH24:MI:SS') as hhmmss
from w_date_ranges
order by 1 ;
/*===== Results =====*/
DT1 DT2 SIGN DDD HH MM SS HHMMSS
------------------- ------------------- ---- --- -- -- -- --------
1964-05-16 23:48:56 1964-05-17 10:23:18 + 0 10 34 22 10:34:22
1985-08-28 22:04:22 1985-08-28 19:15:07 - 0 2 49 15 02:49:15
1991-03-11 02:05:44 1991-03-11 01:49:38 - 0 0 16 6 00:16:06
1998-03-07 06:13:18 1998-03-08 06:13:17 + 0 23 59 59 23:59:59
2001-12-14 01:43:13 2001-12-13 01:43:13 - 1 0 0 0 00:00:00
2003-11-29 18:53:10 2003-11-30 18:53:11 + 1 0 0 1 00:00:01
2009-06-10 10:11:48 2009-06-24 09:23:55 + 13 23 12 7 23:12:07
2009-10-25 00:00:00 2009-10-05 18:47:23 - 19 5 12 37 05:12:37
2010-01-01 13:27:03 2012-04-13 01:00:27 + 832 11 33 24 11:33:24
This example uses the MOD function to extract the hours, minutes, and seconds elements. Note that there are a few dates that would give us erroneous results if we didn't add in the "0.00001" -- there is a slight error in Oracle's calculations and this provides the correction.
/*===== code =====*/
ALTER SESSION set nls_date_format = 'YYYY-MM-DD HH24:MI:SS' ;
with
w_date_ranges as
( select to_date('1964-05-16 23:48:56') dt1, to_date('1964-05-17 10:23:18') dt2 from dual union all
select to_date('1985-08-28 22:04:22') , to_date('1985-08-28 19:15:07') from dual union all
select to_date('1991-03-11 02:05:44') , to_date('1991-03-11 01:49:38') from dual union all
select to_date('1998-03-07 06:13:18') , to_date('1998-03-08 06:13:17') from dual union all
select to_date('2001-12-14 01:43:13') , to_date('2001-12-13 01:43:13') from dual union all
select to_date('2003-11-29 18:53:10') , to_date('2003-11-30 18:53:11') from dual union all
select to_date('2009-06-10 10:11:48') , to_date('2009-06-24 09:23:55') from dual union all
select to_date('2009-10-25 00:00:00') , to_date('2009-10-05 18:47:23') from dual union all
select to_date('2010-01-01 13:27:03') , to_date('2012-04-13 01:00:27') from dual
)
select dt1
, dt2
, regexp_replace(to_char(dt2-dt1,'9999990.000000'),'^([^\.]*)\..*$','\1') as ddd
, trunc(mod( abs(dt2-dt1)*24 + 0.00001, 24 )) as hh
, trunc(mod( abs(dt2-dt1)*24*60 + 0.00001, 60 )) as mm
, trunc(mod( abs(dt2-dt1)*24*60*60 + 0.00001, 60 )) as ss
from w_date_ranges
order by 1 ;
/*===== Results =====*/
DT1 DT2 DDD HH MM SS
------------------- ------------------- ---- -- -- --
1964-05-16 23:48:56 1964-05-17 10:23:18 0 10 34 22
1985-08-28 22:04:22 1985-08-28 19:15:07 -0 2 49 15
1991-03-11 02:05:44 1991-03-11 01:49:38 -0 0 16 6
1998-03-07 06:13:18 1998-03-08 06:13:17 1 23 59 59
2001-12-14 01:43:13 2001-12-13 01:43:13 -1 0 0 0
2003-11-29 18:53:10 2003-11-30 18:53:11 1 0 0 1
2009-06-10 10:11:48 2009-06-24 09:23:55 13 23 12 7
2009-10-25 00:00:00 2009-10-05 18:47:23 -19 5 12 37
2010-01-01 13:27:03 2012-04-13 01:00:27 832 11 33 24
This last example just combines everything into one column:
/*===== code =====*/
ALTER SESSION set nls_date_format = 'YYYY-MM-DD HH24:MI:SS' ;
with
w_date_ranges as
( select to_date('1964-05-16 23:48:56') dt1, to_date('1964-05-17 10:23:18') dt2 from dual union all
select to_date('1985-08-28 22:04:22') , to_date('1985-08-28 19:15:07') from dual union all
select to_date('1991-03-11 02:05:44') , to_date('1991-03-11 01:49:38') from dual union all
select to_date('1998-03-07 06:13:18') , to_date('1998-03-08 06:13:17') from dual union all
select to_date('2001-12-14 01:43:13') , to_date('2001-12-13 01:43:13') from dual union all
select to_date('2003-11-29 18:53:10') , to_date('2003-11-30 18:53:11') from dual union all
select to_date('2009-06-10 10:11:48') , to_date('2009-06-24 09:23:55') from dual union all
select to_date('2009-10-25 00:00:00') , to_date('2009-10-05 18:47:23') from dual union all
select to_date('2010-01-01 13:27:03') , to_date('2012-04-13 01:00:27') from dual
)
select dt1
, dt2
, lpad( case when (dt2-dt1) < 0 then '- ' end
|| case when trunc(abs(dt2-dt1)) >= 1 then '('||trunc(abs(dt2-dt1))||') ' end
|| to_char(trunc(sysdate)+abs(dt2-dt1),'HH24:MI:SS')
, 18
) as "Interval: (days) time"
from w_date_ranges
order by 1 ;
/*===== Results =====*/
DT1 DT2 Interval: (days) time
------------------------- ------------------------- ---------------------
1964-05-16 23:48:56 1964-05-17 10:23:18 10:34:22
1985-08-28 22:04:22 1985-08-28 19:15:07 - 02:49:15
1991-03-11 02:05:44 1991-03-11 01:49:38 - 00:16:06
1998-03-07 06:13:18 1998-03-08 06:13:17 23:59:59
2001-12-14 01:43:13 2001-12-13 01:43:13 - (1) 00:00:00
2003-11-29 18:53:10 2003-11-30 18:53:11 (1) 00:00:01
2009-06-10 10:11:48 2009-06-24 09:23:55 (13) 23:12:07
2009-10-25 00:00:00 2009-10-05 18:47:23 - (19) 05:12:37
2010-01-01 13:27:03 2012-04-13 01:00:27 (832) 11:33:24
No comments:
Post a Comment