Search the Site

Sponsors

bottom corner

Calculating the difference between two dates or times in DB2

This note demonstrates how to calculate the difference between two dates or two times, ie. timestamps, in a DB2 SQL inline function.

This page is filed under keyword(s): 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.

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): db2.
Author: C. Peter Chen
Last updated: 11 Aug 2010

bottom corner