Posted by: Serge Rielau

A co-worker just walked in and asked how to subtract timestamps in SQL.
Easy, you just use the TIMESTAMPDIFF() function.
Now, personally I just plain hate this function because it has some serious shortfalls:

• It approximates the difference between timestamps assuming 365 day years and 30 day months
• It takes some really odd arguments.

So, I think there is a need for an exact and generic function which subtracts dates or timestamps.
There are two ingredients which DB2 supplies natively which come in handy:

• JULIAN_DAY()
  Given a DATE or TIMESTAMP this function returns the number of days since January 1, 4713 BC
• EXTRACT
  Given a TIMESTAMP, DATE or TIME this expression extracts the requested measure

We can now build our own TIMESTAMPDIFF which takes two timestamps and returns the exact difference in fractions of days.

CREATE OR REPLACE FUNCTION TIMESTAMPDIFF(stamp1 TIMESTAMP(12), stamp2 TIMESTAMP(12))
 SPECIFIC TIMESTAMPDIFF
 RETURNS DECFLOAT(34)
 CONTAINS SQL NO EXTERNAL ACTION  DETERMINISTIC
 RETURN ((CAST(JULIAN_DAY(stamp1) AS DECFLOAT(34)) * 86400
          + EXTRACT(HOUR FROM stamp1) * 60 * 60
          + EXTRACT(MINUTE FROM stamp1) * 60
          + EXTRACT(SECOND FROM stamp1))
        -(CAST(JULIAN_DAY(stamp2) AS DECFLOAT(34)) * 86400
          + EXTRACT(HOUR FROM stamp2) * 60 * 60
          + EXTRACT(MINUTE FROM stamp2) * 60
          + EXTRACT(SECOND FROM stamp2)))  
        / 86400;
 
VALUES TIMESTAMPDIFF(CURRENT TIMESTAMP + 1 DAY, CURRENT TIMESTAMP - 1 DAY);
1
------------------------------------------
                            2.000000000000
  1 record(s) selected.
 
VALUES TIMESTAMPDIFF(CURRENT TIMESTAMP + 6 HOURS, CURRENT TIMESTAMP);
1
------------------------------------------
                            0.250000000000
  1 record(s) selected.

Now that was a quick question with a quick answer. :-)
#Db2