DB2 SQL: Dates and Times p.2/2

Date functions

Sometimes, you need to know how the difference between two timestamps. For this, DB2 provides a built in function called TIMESTAMPDIFF(). The value returned is an approximation, however, because it does not account for leap years and assumes only 30 days per month. Here is an example of how to find the approximate difference in time between two dates:

timestampdiff (, char(

timestamp(‘2002-11-30-00.00.00’)-

timestamp(‘2002-11-08-00.00.00’)))

In place of, use one of the following values to indicate the unit of time for the result:

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

Using timestampdiff() is more accurate when the dates are close together than when they are far apart. If you need a more precise calculation, you can use the following to determine the difference in time (in seconds):

(DAYS(t1) – DAYS(t2)) * 86400 +

(MIDNIGHT_SECONDS(t1) – MIDNIGHT_SECONDS(t2))

For convenience, you can also create an SQL user-defined function of the above:

CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP)

RETURNS INT

RETURN (

(DAYS(t1) – DAYS(t2)) * 86400 +

(MIDNIGHT_SECON DS(t1) – MIDNIGHT_SECONDS(t2))

)

@

If you need to determine if a given year is a leap year, here is a useful SQL function you can create to determine the number of days in a given year:

CREATE FUNCTION daysinyear(yr INT)

RETURNS INT

RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE

CASE (mod(yr, 4)) WHEN 0 THEN

CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END

ELSE 365 END

END)@


Go to: Part 1 of 2