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

Postgresql – postgres interval type arithmetic: confirmation of behaviour requested

I’m looking for someone to confirm that I’m understanding this correctly:
when I add intervals together in postgres it looks like the time elements get added and converted – so PT55M + PT10M => PT1H5M
However, PT23H + PT2H => PT25H

I’m guessing this is because you cannot reliably do the arithmetic in the date area without a specific date as a point of reference due to daylight savings and leap years.

The pg documentation says results can "cause unexpected results" – I just want to be sure that if i stick to intervals in hms I’m not ever going to get a number of days back in my answer if I just stick to interval arithmetic

Incidentally the PGInterval java type behaves differently.
There I get an answer of 65 mins. Would that be considered a bug or just a point that nobody uses this class for anything other than getting data in and out of the database?

Tags:

2

Answers


  1. 0

    Your understanding is perfectly correct.

    The difference that comes is because of how time intervals are treated in PostgreSQL. They are meant to be durations, and not intervals therefore the result can be inconsistent. It would be incorrect to consider it a bug just because of the inconsistent results.

    Login or Signup to reply.
  2. 0

    Internally, PostgreSQL stores intervals as seconds, days, and months. The separate elements are necessary because there aren’t consistent conversions between the three types: for example, a calendar day could have 23, 24, or 25 hours due to the effects of daylight savings time. Months have between 28 and 31 days. Interval math is performed independently within each element. Adding seconds such that the result is greater than or equal to 24 hours will not affect the day component, neither will summing day components affect the month component.

    Adding an interval to a timestamp is more complicated because all of the interval elements contribute to the result. The following SQL demonstrates some of the issues that can occur when adding intervals to a timestamp:

    SET TIMEZONE = 'America/New_York';

WITH t(test_ts) AS (
  VALUES (TIMESTAMP WITH TIME ZONE '2023-03-11 12:00:00'))
SELECT test_ts,
       test_ts + 'P1D'::interval AS add_1_day,
       test_ts + 'PT24H'::interval AS add_24_hours,
       test_ts + 'P2D'::interval AS add_2_day,
       test_ts + 'P1DT24H'::interval AS add_1_day_24_hours,
       test_ts + 'PT48H'::interval AS add_48_hours
  FROM t;

    Running this query results in the following:

            test_ts         |       add_1_day        |      add_24_hours      |       add_2_day        |   add_1_day_24_hours   |      add_48_hours      
------------------------+------------------------+------------------------+------------------------+------------------------+------------------------
 2023-03-11 12:00:00-05 | 2023-03-12 12:00:00-04 | 2023-03-12 13:00:00-04 | 2023-03-13 12:00:00-04 | 2023-03-13 12:00:00-04 | 2023-03-13 13:00:00-04
(1 row)

    From these results, it appears that the day element is added to the timestamp before the hour element is added as evidenced by add_1_day_24_hours having a value of 2023-03-13 12:00:00-04 instead of 2023-03-13 13:00:00-04. This behavior should not be relied upon since it is not described in the documentation.

    The apparent difference in behavior between PostgreSQL interval type and Java’s PGInterval type is purely one of presentation choice: the internal representation is the same. It is not a bug.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search