datediff function
The datediff(datepart, start, end) function returns the difference between two date, time or timestamp expressions based on the specified date or time part.
Signatures
| Parameter | Type | Description |
|---|---|---|
| datepart | text | The date or time part to return. Must be one of datepart specifiers. |
| start | date, time, timestamp, timestamptz | The date, time, or timestamp expression to start measuring from. |
| end | date, time, timestamp, timestamptz | The date, time, or timestamp expression to measuring until. |
datepart specifiers
| Specifier | Description |
|---|---|
millenniums, millennium, millennia, mil |
Millennia |
centuries, century, cent, c |
Centuries |
decades, decade, decs, dec |
Decades |
years, year, yrs, yr, y |
Years |
quarter, qtr |
Quarters |
months, month, mons, mon |
Months |
weeks, week, w |
Weeks |
days, day, d |
Days |
hours, hour, hrs, hr, h |
Hours |
minutes, minute, mins, min, m |
Minutes |
seconds, second, secs, sec, s |
Seconds |
milliseconds, millisecond, mseconds, msecs, msec, ms |
Milliseconds |
microseconds, microsecond, useconds, usecs, usec, us |
Microseconds |
Examples
To calculate the difference between two dates in millennia:
SELECT datediff('millennia', '2000-12-31', '2001-01-01') as d;
d
-----
1
Even though the difference between 2000-12-31 and 2001-01-01 is a single day, a millennium boundary is crossed from one date to the other, so the result is 1.
To see how this function handles leap years:
SELECT datediff('day', '2004-02-28', '2004-03-01') as leap;
leap
------------
2
SELECT datediff('day', '2005-02-28', '2005-03-01') as non_leap;
non_leap
------------
1
In the statement that uses a leap year (2004), the number of day boundaries crossed is 2. When using a non-leap year (2005), only 1 day boundary is crossed.