doc/user/content/sql/functions/datediff.md
The datediff(datepart, start, end) function returns the difference between two date, time or timestamp expressions based on the specified date or time part.
| 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 |
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.