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.

One Reply to “Calculating the difference between two dates or times in DB2”

Leave a Reply

Your email address will not be published. Required fields are marked *