TIMESTAMPDIFF

The TIMESTAMPDIFF function returns the total number of specified intervals between two timestamps.

SQL syntax

TIMESTAMPDIFF (Interval, TimestampExpression1, TimestampExpression2)

Parameters

TIMESTAMPDIFF has the parameters:

Parameter Description

Interval

Specified interval. Must be expressed as literal. Valid values are listed in the description section.

TimestampExpression1

Datetime expressions. Valid data types are ORA_DATE, ORA_TIMESTAMP, TT_DATE, and TT_TIMESTAMP. (The alias DATE and TIMESTAMP data types are also valid.) TT_TIME is not supported.

TimestampExpression2

Datetime expressions. Valid data types are ORA_DATE, ORA_TIMESTAMP, TT_DATE, and TT_TIMESTAMP. (The alias DATE and TIMESTAMP data types are also valid.) TT_TIME is not supported.

Description

  • Valid values for Interval are:

    • SQL_TSI_FRAC_SECOND

    • SQL_TSI_SECOND

    • SQL_TSI_MINUTE

    • SQL_TSI_HOUR

    • SQL_TSI_DAY

    • SQL_TSI_WEEK

    • SQL_TSI_MONTH

    • SQL_TSI_QUARTER

    • SQL_TSI_YEAR

  • SQL_TSI_FRAC_SECOND is expressed in billionths of a second.

  • Interval determines the units in which the difference in timestamps is returned. For example, if you specify SQL_TSI_YEAR, the difference in timestamps is returned in years.

  • TimesTen returns the result as the difference between TimestampExpression2 minus (-) TimestampExpression1. The return type is TT_BIGINT.

  • Only positive timestamp expressions (0001-01-01) are allowed. For TT_DATE and TT_TIMESTAMP, because the starting range for these data types is 1753-01-01, the timestamp expression must be equal to or greater than this date.

  • If TimestampExpression1 or TimestampExpression2 is NULL, then the result is NULL.

  • If either timestamp expression is a date value and Interval specifies fractional seconds, seconds, minutes, or hours, the time portion of the timestamp is set to 0 before TimesTen calculates the difference between the timestamps.

  • The function first expresses each of the timestamps in units of the specified Interval by converting the higher order interval type to the specified interval type. For example, TimesTen converts years to months if the specified interval is months. Thus, one year is 12 months, one week is seven days, and so on. To find the number of days between two timestamps, the exact number of days is computed. Since months vary in the number of days, TimesTen does not make an assumption about the number of days in a month.

  • The function increments the specified interval whenever fractional intervals cross an interval boundary. For example, the difference in years between 2009-12-31 and 2010-01-01 is one year because the fractional year represents a crossing from one year to the next (2009 to 2010). However, the difference between 2010-01-01 and 2010-12-31 is zero years because the fractional interval does not cross a boundary. It falls within the year 2010.

  • The function calculates the difference in weeks by first calculating the difference in days and then divides the result by seven before rounding. TimesTen assumes a week starts on a Sunday. Therefore the difference in weeks between 2010-10-21 (a Thursday) and 2010-10-25 (the following Monday) results in a value of one week. The difference in the same dates, if Tuesday denoted the start of the week, would result in zero weeks.

Examples

Use the TIMESTAMPDIFF function to calculate the difference in days between dates 2008-02-01 and 2008-03-01. Because 2008 is a leap year, the result is 29 days. The calculation is precise with no assumption of a 30-day month. 

Command> SELECT TIMESTAMPDIFF (SQL_TSI_DAY, DATE '2008-02-01', DATE '2008-03-01') FROM dual;
< 29 >
1 row found.

Use the TIMESTAMPDIFF function to calculate the difference in months between dates 2009-02-01 and 2009-03-01. Because there is a crossing of the interval month boundary, the function returns 1. In the second example, because days is specified for the interval, the result is 28. 

Command> SELECT TIMESTAMPDIFF (SQL_TSI_MONTH, DATE '2009-02-01', DATE '2009-03-01') 
           FROM dual;
< 1 >
1 row found.

Command> SELECT TIMESTAMPDIFF (SQL_TSI_DAY, DATE '2009-02-01', DATE '2009-03-01') 
           FROM dual;
< 28 >
1 row found.

Use the TIMESTAMPDIFF function to calculate the difference in months between dates 2009-02-01 and 2009-02-29. Because there is not a crossing of the interval month boundary, the function returns 0. 

Command> SELECT TIMESTAMPDIFF (SQL_TSI_MONTH, DATE '2009-02-01', DATE '2009-02-28') 
           FROM dual;
< 0 >
1 row found.

Use the TIMESTAMPDIFF function to illustrate the time difference in fractional seconds between mixed types. The time difference of one hour is returned in nanoseconds (unit for fractional seconds). The time element of the data type is set to 00:00:00. 

Command> SELECT TIMESTAMPDIFF (SQL_TSI_FRAC_SECOND, TT_TIMESTAMP '2009-12-31 01:00:00.00', 
           DATE '2009-12-31') FROM dual;
< -3600000000000 >
1 row found.