Systems Engineering and RDBMS

Back to basics: Date difference in seconds (Oracle)

Posted by decipherinfosys on March 14, 2008

It is a common requirement to do date arithmetic and to get difference between two date values. We have to find out that how many seconds were taken for each message to get processed. Apart from other columns, we have two date columns in the table:

create_date_time and mod_date_time

Create_date_time gets populated when a record is created in the table for the first time and once the message is processed successfully, mod_date_time gets updated. Let’s see how we can get the difference between these two dates in seconds.

Connect to SQL*Plus with proper authentication. Create the following table and populate it with some data using the scripts shown below:

CREATE TABLE T1
(T1_ID NUMBER(9),
CREATE_DATE_TIME DATE,
MOD_DATE_TIME DATE);

INSERT INTO T1(T1_ID,CREATE_DATE_TIME,MOD_DATE_TIME)
SELECT 1,TO_DATE(’03/13/2008 07:25:35′,’MM/DD/YYYY HH24:MI:SS’), TO_DATE(’03/13/2008 07:25:49′,’MM/DD/YYYY HH24:MI:SS’)
FROM DUAL
UNION ALL
SELECT 2,TO_DATE(’03/13/2008 10:12:21′,’MM/DD/YYYY HH24:MI:SS’), TO_DATE(’03/13/2008 11:11:25′,’MM/DD/YYYY HH24:MI:SS’)
FROM DUAL
UNION ALL
SELECT 3,TO_DATE(’03/13/2008 14:19:22′,’MM/DD/YYYY HH24:MI:SS’), TO_DATE(’03/13/2008 14:20:10′,’MM/DD/YYYY HH24:MI:SS’)
FROM DUAL
UNION ALL
SELECT 4,TO_DATE(’03/13/2008 23:58:58′,’MM/DD/YYYY HH24:MI:SS’), TO_DATE(’03/14/2008 00:01:05′,’MM/DD/YYYY HH24:MI:SS’)
FROM DUAL
UNION ALL
SELECT 5,TO_DATE(’03/13/2008 19:21:55′,’MM/DD/YYYY HH24:MI:SS’), TO_DATE(’03/13/2008 19:22:02′,’MM/DD/YYYY HH24:MI:SS’)
FROM DUAL;

COMMIT;

Simplest way to get the time difference is to obtain the date difference which is given in days and then multiply it by 24*60*60 to get the end results in seconds. Another way to get the difference in seconds is by using the EXTRACT function. We can get the day, hour, minute and seconds using EXTRACT function and then convert it into seconds and add them up together to derive at final seconds.

Following SQL shows how one can extract different date parts using the EXTRACT function.

SELECT t1_id,
EXTRACT(Day FROM(mod_date_time – create_date_time) DAY TO SECOND) as Day,
EXTRACT(HOUR FROM(mod_date_time – create_date_time) DAY TO SECOND) as Hour,
EXTRACT(Minute FROM(mod_date_time – create_date_time) DAY TO SECOND) as Minute,
EXTRACT(SECOND FROM(mod_date_time – create_date_time) DAY TO SECOND) as second
FROM t1;

Result is as shown below.

     T1_ID        DAY       HOUR     MINUTE     SECOND
---------- ---------- ---------- ---------- ----------
         1          0          0          0         14
         2          0          0         59          4
         3          0          0          0         48
         4          0          0          2          7
         5          0          0          0          7

Since we have extracted date parts, we can convert them into seconds to get the difference in seconds. Following query shows both ways of getting difference in seconds.

SELECT t1_id, to_date(create_date_time,’MM/DD/YYYY HH24:MI:SS’) create_date_time,
to_date(mod_date_time, ‘MM/DD/YYYY HH24:MI:SS’) mod_date_time,
/* Simplest one */
(mod_date_time – create_date_time)*24*60*60 as sec_1,
/* Using Extract */
EXTRACT(Day FROM(mod_date_time – create_date_time) DAY TO SECOND)*86400 +
EXTRACT(HOUR FROM(mod_date_time – create_date_time) DAY TO SECOND)*3600 +
EXTRACT(Minute FROM(mod_date_time – create_date_time) DAY TO SECOND)*60 +
EXTRACT(SECOND FROM(mod_date_time – create_date_time) DAY TO SECOND) as sec_2
FROM t1;

And here is the result.

     T1_ID CREATE_DATE_TIME    MOD_DATE_TIME       SEC_1 SEC_2
---------- ------------------- ------------------- ----- -----
         1 03/13/2008 07:25:35 03/13/2008 07:25:49    14    14
         2 03/13/2008 10:12:21 03/13/2008 11:11:25   3544 3544
         3 03/13/2008 14:19:22 03/13/2008 14:20:10     48   48
         4 03/13/2008 23:58:58 03/14/2008 00:01:05    127  127
         5 03/13/2008 19:21:55 03/13/2008 19:22:02      7    7

Since message has to be processed in a couple of seconds, we are assuming that we will not have any message where difference between these two dates is more than a few minutes but for safety sakes, we are also taking into account ‘days’ in case the message sits there for more than 1 day in an unprocessed status. Above example is for illustration only. One can also write generic function(s) to get the time difference either in hours, minutes or seconds.

One Response to “Back to basics: Date difference in seconds (Oracle)”

  1. […] Back to basics: Date difference in seconds (Oracle … – Mar 14, 2008  · It is a common requirement to do date arithmetic and to get difference … Date difference in seconds (Oracle) … Systems Engineering and RDBMS … […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: