TIMESTAMPDIFF

Returns the time span between two TIMESTAMP or TIMESTAMPTZ values, in the intervals specified.

Returns the time span between two TIMESTAMP or TIMESTAMPTZ values, in the intervals specified. TIMESTAMPDIFF excludes the start date in its calculation.

Behavior type

  • Immutable if start and end dates are TIMESTAMP

  • Stable if start and end dates are TIMESTAMPTZ

Syntax

TIMESTAMPDIFF ( datepart, start, end );

Parameters

datepart
Specifies the type of date or time intervals that TIMESTAMPDIFF returns. If datepart is an expression, it must be enclosed in parentheses:
TIMESTAMPDIFF((expression), start, end );

datepart must evaluate to one of the following string literals, either quoted or unquoted:

  • year | yy | yyyy

  • quarter | qq | q

  • month | mm | m

  • day | dayofyear | dd | d | dy | y

  • week | wk | ww

  • hour | hh

  • minute | mi | n

  • second | ss | s

  • millisecond | ms

  • microsecond | mcs | us

start, end
Specify the start and end dates, where start and end evaluate to one of the following data types:

If end < start, TIMESTAMPDIFF returns a negative value.

Date part intervals

TIMESTAMPDIFF uses the datepart argument to calculate the number of intervals between two dates, rather than the actual amount of time between them. For detailed information, see DATEDIFF.

Examples

=> SELECT TIMESTAMPDIFF (YEAR,'1-1-2006 12:34:00', '1-1-2008 12:34:00');
 timestampdiff
---------------
             2
(1 row)

See also

DATEDIFF