INTERVAL advantages
I have blogged about the disadvantges of using integers instead of proper date
or timestamp
values.
Another (frequently ignored) advantage of using date
or timestamp
values, is the fact that it’s easier to do date arithmetics with them - especially when combined with the interval
data type.
The interval
defines a “duration” and can do so using “context aware” units, using months, days or hours. Especially “one month” isn’t a unit that has a fixed length, and it’s impossible to properly store such a duration with an integer - at least not with a lot of complicated math.
The database also knows about leap years and daylight savings time.
date '2020-02-20' + interval '15' day
returns 2020-03-06
However,
date '2021-02-20' + interval '15' day
returns the 2021-03-07
because 2021 is not a leap year.
The unit of “one month” also varies depending the point in time the calculation starts.
The expression date '2020-01-01' + interval '1 month'
yields (unsurprisingly) the first of Feburary. The expression date '2020-02-01' + interval '1 month'
correctly returns the first of March.
So depending on the starting point, the interval
type correctly resolves the length of the month.
Daylight saving times are also taken into account (when using timestamp with time zone
).
timestamptz '2021-03-28 03:00:00 Europe/Berlin' - timestamptz '2021-03-28 00:00:00 Europe/Berlin'
yields a duration of 2 hours rather than 3 because of advancing the clock by one hour.
Similarily,
timestamptz '2021-03-28 02:00:00 Europe/Berlin' + interval '1 hour';
yields 04:00
, not 03:00
- again because the clock is advanced one hour during that time.