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:

TIMESTAMP/TIMESTAMPTZ
TIMESTAMPTZ

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)