Tags

, ,

The hard way and the smart way

A problem that often arises in the classroom is how to calculate the difference (hours, minutes and seconds) of two DATE values.

The hard way

At various places on the web you may find solutions like this:

WITH example_data AS (SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('01.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL)
SELECT TO_CHAR(TRUNC((date_to - date_from) * 24),'FM00') || ':' 
    || TO_CHAR(TRUNC(((date_to - date_from) * 24 - TRUNC((date_to - date_from) * 24)) * 60),'FM00') || ':' 
    || TO_CHAR(((date_to - date_from) * 24 * 60 - TRUNC((date_to - date_from) * 24 * 60)) * 60,'FM00') AS time
  FROM example_data;

TIME
-----------
03:46:51

Personally I would call this the hard way to solve the problem, because there is a much easier solution for this problem.

The smart way

WITH example_data AS (SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('01.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL)
SELECT TO_CHAR(TRUNC(SYSDATE) + (date_to - date_from),'HH24:MI:SS') as TIME
  FROM example_data;

TIME
--------
03:46:51

This solution makes use of the date arithmetic logic of ORACLE where the difference between two dates values is expressed as a number representing the number of days including a fraction of days. Furthermore adding a number to a date again results in a date data type. So if we add the difference of two date values to midnight and use TO_CHAR to display the time component of the result, this result will express the difference of those two dates. Now what should we do, if the difference is more than one day?
We just could make use of the day in year format after having added the difference between the two date values to the 31st of december.

WITH example_data AS (SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('01.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL
                      UNION ALL
                      SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('03.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL)
SELECT CASE
          WHEN TRUNC(date_to - date_from) > 0
             THEN TO_CHAR((TRUNC(SYSDATE,'year')-1) + (date_to - date_from),'DDD HH24:MI:SS')
          ELSE TO_CHAR(TRUNC(SYSDATE) + (date_to - date_from),'HH24:MI:SS')
       END as TIME
  FROM example_data;

TIME
------------
03:46:51
002 03:46:51

This solution represents in my opinion the smart way to calculate time difference between two date values.