Add a number of days to a timestamp

All we need is an easy explanation of the problem, so here it is.

I’d like to add (or rather subtract) a number of days from a timestamp.

For example from 2020-04-13 00:00:00 Europe/Berlin I want to subtract 20 days and I expect the result to be 2020-03-24 00:00:00 Europe/Berlin.

If I run

select ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - INTERVAL '20 DAY') AT TIME ZONE 'Europe/Berlin'

I get 2020-03-23 23:00:00.000000.

That seems to be because PostgreSQL subtracts 20 * 24 hours (a DST switch happens during that date range) which is different from subtracting 20 days.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The interval calculation is done in whatever timezone your session is set to. This is documented here:

When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances or decrements the date of the timestamp with time zone by the indicated number of days, keeping the time of day the same. Across daylight saving time changes (when the session time zone is set to a time zone that recognizes DST), this means interval ‘1 day’ does not necessarily equal interval ’24 hours’

I don’t know what your session timezone is set to (you can do show timezone; to see), but apparently it is not ‘Europe/Berlin’. When set to ‘Europe/Berlin’, I get the answer you expected. Whatever your session timezone is set to either doesn’t do DST, or doesn’t implement the transition during the time period in question. All if its days during this period really are 24 hours.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply