TIMESTAMPDIFF

The TIMESTAMPDIFF function finds the difference between two timestamps, in terms of the specfied interval.

Syntax

TIMESTAMPDIFF ( interval, timeStamp1, timeStamp2 )

interval

One of the following timestamp interval constants:

  • 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

timeStamp1

The first timestamp value.

timeStamp2

The second timestamp value.

If you use a datetime column inside the TIMESTAMPDIFF function in a WHERE clause, the optimizer cannot use indexes on that column. We strongly recommend not doing this!

Results

The TIMESTAMPDIFF function returns an integer value representing the count of intervals between the two timestamp values.

Examples

These examples shows the number of years a player was born after Nov 22, 1963:.

splice> SELECT ID, BirthDate, TIMESTAMPDIFF(SQL_TSI_YEAR, Date('11/22/1963'), BirthDate) "YearsSinceJFK"
   FROM Players WHERE ID < 11
   ORDER BY Birthdate;
ID    |BIRTHDATE |YearsSinceJFK
--------------------------------------
7     |1981-07-02|17
6     |1982-01-05|18
8     |1983-04-13|19
10    |1983-11-06|19
9     |1983-12-24|20
4     |1987-01-21|23
1     |1987-03-27|23
2     |1988-04-20|24
3     |1990-10-30|26
5     |1991-01-15|27

10 rows selected

See Also