I have blogged about the disadvantges of using integers instead of proper
Another (frequently ignored) advantage of using
timestamp values, is the fact that it’s easier to do date arithmetics with them - especially when combined with the
interval data type.
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
date '2021-02-20' + interval '15' day
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.
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.
timestamptz '2021-03-28 02:00:00 Europe/Berlin' + interval '1 hour';
03:00 - again because the clock is advanced one hour during that time.