TIMESTAMPDIFF scalar function
Returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps.
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.
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:
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 |
- 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
TIMESTAMPDIFF(4,CHAR(TIMESTAMP('2001-09-29-11.25.42.483219') -
TIMESTAMP('2001-09-26-12.07.58.065497')))