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


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.


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.