Calculating the difference between two dates or times in DB2

To do so, we can utilize the timestampdiff() function provided by DB2. This function takes in two parameters. The first parameter is a numeric value indicating the unit in which you wish to receive the results in; the values must be one of the following.

  • 1 : Fractions of a second
  • 2 : Seconds
  • 4 : Minutes
  • 8 : Hours
  • 16 : Days
  • 32 : Weeks
  • 64 : Months
  • 128 : Quarters of a year
  • 256 : Years

The second parameter should contain the subtraction formula between two timestamps, with the results converted to character format. Below is one example of usage.

select 
timestampdiff(
  16, 
  char(timestamp('2010-01-11-15.01.33.453312') - current timestamp))  
from sysibm.sysdummy1;

The result from this statement is 210 at the time of the writing. Notice that even though the first timestamp is set to be prior than the current timestamp, the outcome is still positive — This function returns the absolute value (ie. always positive) reflecting the difference in time between two timestamps. Also, take note that the result will always be an integer, thus it can only be considered an estimation of the date/time difference rather than an exact one.

2 Replies to “Calculating the difference between two dates or times in DB2”

  1. Hi Thanks for this. Helped me do some timestamp differences. Just one thing, you say it is only considered an estimation. However I wanted to see the hours difference between 2 datetime stamps. I used a lower Value of minutes and then divide it by 60 and then cast it and get a more accurate value. For example:

    I have 2 timestamps
    2015-02-24 14:23:29.000000
    And 2015-02-24 10:09:15.000000

    If I use the timestampdiff with hours it just gives 4 hrs as the result
    But if you use minutes and then divide that by 60 I get 4.23 hrs

    Cast(timestampdiff(4, Char(timestamp(2015-02-24 14:23:29.000000 )-Timestamp(2015-02-24 10:09:15.000000)))as decimal(10,2))/60)

    Thanks

Leave a Reply to Paul Solerti Cancel reply

Your email address will not be published.