TIMESTAMPDIFF scalar function

Returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps.

Read syntax diagramSkip visual syntax diagramTIMESTAMPDIFF(numeric-expression,string-expression)

The schema is SYSIBM. The SYSFUN version of the TIMESTAMPDIFF function continues to be available.

numeric-expression
An expression that returns a value of built-in INTEGER or SMALLINT data type. Valid values represent an interval as defined in the following table.
Table 1. Valid interval values
Value Interval
1 Microseconds
2 Seconds
4 Minutes
8 Hours
16 Days
32 Weeks
64 Months
128 Quarters
256 Years
string-expression
An expression that returns a value of built-in CHAR or VARCHAR data type. The value is expected to be the result of subtracting two timestamps and converting the result to CHAR. If the value is not a CHAR or VARCHAR data type, it is implicitly cast to VARCHAR before evaluating the function. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed

If a positive or negative sign is present, it is the first character of the string. The following table describes the elements of the character string duration.

Table 2. TIMESTAMPDIFF string elements
String Elements Valid Values Character position from the decimal point (negative is left)
Years 1-9998 or blank -14 to -11
Months 0-11 or blank -10 to -9
Days 0-30 or blank -8 to -7
Hours 0-24 or blank -6 to -5
Minutes 0-59 or blank -4 to -3
Seconds 0-59 -2 to -1
Decimal Points Period 0
Microseconds 000000-999999 1 to 6

The result of the function is INTEGER with the same sign as the second argument. The result can be null; if the argument is null, the result is the null value.

The returned value is determined for each interval as indicated by the following table:

Table 3. TIMESTAMPDIFF computations
Result interval Computation using duration elements
Years Years
Quarters integer value of (months+(years*12))/3
Months months + (years*12)
Weeks integer value of ((days+(months*30))/7)+(years*52)
Days days + (months*30)+(years*365)
Hours hours + ((days+(months*30)+(years*365))*24)
Minutes (the absolute value of the duration must not exceed 40850913020759.999999) minutes + (hours+((days+(months*30)+(years*365))*24))*60
Seconds (the absolute value of the duration must be less than 680105031408.000000) seconds + (minutes+(hours+((days+(months*30)+(years*365))*24))*60 )*60
Microseconds (the absolute value of the duration must be less than 3547.483648) microseconds + (seconds+(minutes*60))*1000000
The following assumptions may be used in estimating a difference:
  • There are 365 days in a year.
  • There are 30 days in a month.
  • There are 24 hours in a day.
  • There are 60 minutes in an hour.
  • There are 60 seconds in a minute.

These assumptions are used when converting the information in the second argument, to the interval type specified in the first argument. The returned estimate may vary by a number of days. For example, if the number of days (interval 16) is requested for the difference between '1997-03-01-00.00.00' and '1997-02-01-00.00.00', the result is 30. This is because the difference between the timestamps is 1 month, and the assumption of 30 days in a month applies.

Example

The following example returns 4277, the number of minutes between two timestamps: 
   TIMESTAMPDIFF(4,CHAR(TIMESTAMP('2001-09-29-11.25.42.483219') -
     TIMESTAMP('2001-09-26-12.07.58.065497')))