The TIMESTAMPDIFF function returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps.
| Valid values for numeric-expression | Equivalent intervals |
|---|---|
| 1 | Microseconds |
| 2 | Seconds |
| 4 | Minutes |
| 8 | Hours |
| 16 | Days |
| 32 | Weeks |
| 64 | Months |
| 128 | Quarters |
| 256 | Years |
| 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 separator | period | 0 |
| Microseconds | 000000-999999 | 1 to 6 |
The result of the function is an integer with the same sign as string-expression. If either argument can be null, the result can be null; if either argument is null, the result is the null value.
| 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 are used when converting the element values to the requested interval type:
TIMESTAMPDIFF(16, CHAR(TIMESTAMP('1997-03-01-00.00.00') - TIMESTAMP('1997-02-01-00.00.00') ) )
The
result of the timestamp arithmetic is a duration of 00000100000000.000000,
or 1 month. When the TIMESTAMPDIFF function is invoked with 16 for
the interval argument (days), the assumption of 30 days in a month
is applied and the result is 30.TIMESTAMPDIFF(16, CHAR(TIMESTAMP('1997-03-01-00.00.00') - TIMESTAMP('1997-02-02-00.00.00') ) )
The
result of the timestamp arithmetic is a duration of 00000027000000.000000,
or 27 days. When the TIMESTAMPDIFF function is invoked with 16 for
the interval argument (days), the result is 27.TIMESTAMPDIFF(64, CHAR(TIMESTAMP('1997-09-01-00.00.00') - TIMESTAMP('1997-08-02-00.00.00') ) )
The
result of the timestamp arithmetic is a duration of 00000030000000.000000,
or 30 days. When the TIMESTAMPDIFF function is invoked with 64 for
the interval argument (months), the result is 0. The days portion
of the duration is 30, but it is ignored because the interval specified
months.SELECT TIMESTAMPDIFF(64, CAST(CURRENT_TIMESTAMP-CAST(BIRTHDATE AS TIMESTAMP) AS CHAR(22))) AS AGE_IN_MONTHS FROM EMPLOYEE