Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

TIMESTAMPDIFF function (ODBC compatible)

Syntax

TIMESTAMPDIFF(interval, date_time_exp1, date_time_exp2)

interval::

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

Description

Returns an integer representing the number of intervals by which date_time_exp2 is greater than date_time_exp1.

Arguments

  • interval - Keywords that specify the interval in which to express the difference between the two date-time arguments. The SQL_TSI_FRAC_SECOND keyword specifies fractional seconds in milliseconds.
  • date_time_exp1 - A date-time expression which TIMESTAMPDIFF subtracts from date_time_exp2.
  • date_time_exp2 - A date-time expression from which TIMESTAMPDIFF subtracts date_time_exp1.

Example

The following example displays difference in seconds between the current system time and one day later.

> select timestampdiff(sql_tsi_second, sysdate, sysdate + 1) from syscalctable;

86400

-----

86400

Notes

If either date-time expression is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of that expression is set to the current date before calculating the difference between the expressions.

If either date-time expression is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of that expression is set to 0 before calculating the difference between the expressions.

TOCIndex