TIMESTAMPDIFF

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


>>-TIMESTAMPDIFF(numeric-expression,string-expression)---------><

The schema is SYSIBM.

numeric-expression

An expression that returns a value that is a built-in SMALLINT or INTEGER data type. The value specifies the interval that is used to determine the difference between two timestamps. The following table lists the valid values for numeric-expression:

Table 1. Valid values for numeric-expression and equivalent intervals that are used to determine 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-expression

Start of change An expression that returns a value of a built-in character string or a graphic string data type that is not a LOB. The value is expected to be the result of subtracting two timestamps and converting the result to a character string of length 22. The string value must not have more than 6 digits to the right of a decimal point. End of change

If the supplied argument is a graphic string, it is first converted to a character string before the function is executed.

The following table describes the elements of string-expression:

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 separator	period	0
Microsecond	000000-999999	1 to 6

The result of the function is an integer with the same sign as the second argument.

The result can be null; if any 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+(months30))/7)+(years52)
Days	days + (months30)+(years365)
Hours	hours+ ((days + (months30)+(years365))*24)
Minutes (the absolute value of the duration must not exceed 40850913020759.999999)	minutes + (hours+((days+(months30)+(years365))24))60
Seconds (the absolute value of the duration must be less than 680105031408.000000)	seconds + (minutes+(hours+((days+(months30) +(years365))24))60 )*60
Microseconds (the absolute value of the duration must be less than 3547.483648)	microseconds + (seconds+(minutes60))1000000

The following assumptions are used in estimating a difference:

One year has 365 days
One year has 52 weeks
One year has 12 months
One month has 30 days
One day has 24 hours
One hour has 60 minutes
One minute has 60 seconds

The use of these assumptions imply that some result values are an estimate of the interval. Consider the following examples:

Difference of 1 month where the month has less than 30 days.
```
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.
Difference of 1 day less than 1 month where the month has less than 30 days.
```
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.
Difference of 1 day less than 1 month where the month has 31 days.
```
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.

Example: The following statement estimates the age of employees in months and returns that value as AGE_IN_MONTHS:

SELECT
  TIMESTAMPDIFF(64, CAST(CURRENT_TIMESTAMP-CAST(BIRTHDATE AS TIMESTAMP)
                AS CHAR(22)))
    AS AGE_IN_MONTHS
  FROM EMPLOYEE;