1

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.

1
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.