Question 89267 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql: Issue with daylight saving and interval

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:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search