TIMESTAMPDIFF

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>-TIMESTAMPDIFF--(--numeric-expression--,--string-expression--)-><
 

The TIMESTAMPDIFF function returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps.

numeric-expression
The first argument must be a built-in data type of either INTEGER or SMALLINT. Valid values of interval (the first argument) are:
1 Fractions of a second
2 Seconds
4 Minutes
8 Hours
16 Days
32 Weeks
64 Months
128 Quarters
256 Years
string-expression
string-expression is the result of subtracting two timestamps and converting the result to a string of length 22. The argument must be an expression that returns a value of a built-in character string or a graphic string.

If string-expression is a character or graphic string, it must not be a CLOB or DBCLOB.

The result of the function is an integer. If either argument can be null, the result can be null; if either argument is null, the result is the null value.

The following assumptions may be used in estimating the difference:

These assumptions are used when converting the information in the second argument, which is a timestamp duration, 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 a difference in timestamps for '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 so the assumption of 30 days in a month applies.

Example