Postgresql: Issue with daylight saving and interval

Kamahl
March 24, 2023
274 views
0 votes
2 Answers

In Postgresql I use interval to detect if two timestamps differs by less than 1 month,
the problem is that with daylight saving, there’s a mismatch of 1 hour.
For example considering

SELECT
'2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago' < '2023-01-01 15:00:00'::timestamp at time zone 'America/Chicago' + interval '1 months',
'2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' < '2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago' + interval '1 months',
'2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago' < '2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' + interval '1 months',
'2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago' < '2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago' + interval '1 months',
'2023-06-01 15:00:00'::timestamp at time zone 'America/Chicago' < '2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago' + interval '1 months'
;

we can clearly see that the check fails for 2023-04-01 and 2023-03-01, because in that period daylight saving goes on.
To me the problem seems that interval only increases the timestamp by 1 month, without considering the daylight saving.
I mean '2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' + interval '1 months' (or the equivalent '2023-03-01 15:00:00 America/Chicago'::timestamptz + interval '1 months') should increase 2023-03-01 by one month, but should also decrease the hour by 1, but it doesn’t.

Is there any way to make interval daylight saving aware?

I’m on postgres 11.16 if that matters, but I can’t upgrade it in case.

Tags: postgresql

Answers

Chosen as BEST ANSWER

Actually it seems that I just need to localise to the time zone after increasing with interval, so

SELECT
'2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago' < '2023-01-01 15:00:00'::timestamp at time zone 'America/Chicago' + interval '1 months',
'2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' < '2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago' + interval '1 months',
'2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago' < '2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' + interval '1 months',
'2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago' < '2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago' + interval '1 months',
'2023-06-01 15:00:00'::timestamp at time zone 'America/Chicago' < '2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago' + interval '1 months'
;

becomes

SELECT
'2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago' < ('2023-01-01 15:00:00'::timestamp + interval '1 months') at time zone 'America/Chicago',
'2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' < ('2023-02-01 15:00:00'::timestamp + interval '1 months') at time zone 'America/Chicago',
'2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago' < ('2023-03-01 15:00:00'::timestamp + interval '1 months') at time zone 'America/Chicago',
'2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago' < ('2023-04-01 15:00:00'::timestamp + interval '1 months') at time zone 'America/Chicago',
'2023-06-01 15:00:00'::timestamp at time zone 'America/Chicago' < ('2023-05-01 15:00:00'::timestamp + interval '1 months') at time zone 'America/Chicago'
;

I'll have to double check if I'm missing some details/edge case, but it might just be as simple as that.

(Edit)

Datetimes

Fractional parts of weeks and days are computed to be an integer number of days and microseconds, assuming 30 days per month and 24 hours per day, e.g., ‘1.75 months’ becomes 1 mon 22 days 12:00:00.

So:


select '30 days'::interval = '1 month'::interval;
t

select '2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago' - '2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' ;

30 days 23:00:00

select '2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' + '1 month'::interval ;
        ?column?        
------------------------
 2023-04-01 13:00:00-07

UPDATE

When in doubt use UTC:

SELECT
'2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago' at time zone 'UTC' < ('2023-01-01 15:00:00'::timestamp at time zone 'America/Chicago' at time zone 'UTC'+ interval '1 months'),
'2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' at time zone 'UTC' < ('2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago' at time zone 'UTC' + interval '1 months'),
'2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago' at time zone 'UTC' < ('2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' at time zone 'UTC' + interval '1 months'),
'2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago' at time zone 'UTC' < ('2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago' at time zone 'UTC' + interval '1 months'),
'2023-06-01 15:00:00'::timestamp at time zone 'America/Chicago' at time zone 'UTC' < ('2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago' at time zone 'UTC' + interval '1 months')
;

 ?column? | ?column? | ?column? | ?column? | ?column? 
----------+----------+----------+----------+----------
 f        | f        | t        | f        | f

Please signup or login to give your own answer.

Click here to cancel reply.