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.
Value | Interval |
---|---|
1 | Microseconds |
2 | Seconds |
4 | Minutes |
8 | Hours |
16 | Days |
32 | Weeks |
64 | Months |
128 | Quarters |
256 | Years |
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.
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:
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 |
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.
TIMESTAMPDIFF(4,CHAR(TIMESTAMP('2001-09-29-11.25.42.483219') -
TIMESTAMP('2001-09-26-12.07.58.065497')))