Hi,
On 2023-05-18 17:03:24 -0400, Tom Lane wrote:
> Andres Freund <and...@anarazel.de> writes:
> > What about an interval / interval -> double operator that errors out
> > whenever
> > month is non-zero? As far as I can tell that would always be deterministic.
>
> We have months, days, and microseconds, and microseconds-per-day isn't
> much more stable than days-per-month (because DST).
I was about to counter that, if you subtract a timestamp before/after DST
changes, you currently don't get a full day for the "shorter day":
SET timezone = 'America/Los_Angeles';
SELECT '2023-03-13 23:00:00-07'::timestamptz - '2023-03-11
23:00:00-08'::timestamptz;
┌────────────────┐
│ ?column? │
├────────────────┤
│ 1 day 23:00:00 │
└────────────────┘
which afaics would make it fine to just use 24h days when dividing intervals.
However, that seems to lead to quite broken results:
SET timezone = 'America/Los_Angeles';
WITH s AS (SELECT '2023-03-11 23:00-08'::timestamptz a, '2023-03-13
23:00-07'::timestamptz b) SELECT a, b, b - a AS b_min_a, a + (b - a) FROM s;
┌────────────────────────┬────────────────────────┬────────────────┬────────────────────────┐
│ a │ b │ b_min_a │
?column? │
├────────────────────────┼────────────────────────┼────────────────┼────────────────────────┤
│ 2023-03-11 23:00:00-08 │ 2023-03-13 23:00:00-07 │ 1 day 23:00:00 │ 2023-03-13
22:00:00-07 │
└────────────────────────┴────────────────────────┴────────────────┴────────────────────────┘
I subsequently found a comment that seems to reference this in timestamp_mi().
/*----------
* This is wrong, but removing it breaks a lot of regression tests.
* For example:
*
How's this not a significant bug that we need to fix?
I'm not sure this ought to be fixed in timestamp_mi() - perhaps the order of
operations in timestamp_pl_interval() would be a better place?
We probably should document that interval math isn't associative:
postgres[2807421][1]=# SELECT ('2023-03-11 23:00:00-08'::timestamptz + '1
day'::interval) + '23h'::interval;
┌────────────────────────┐
│ ?column? │
├────────────────────────┤
│ 2023-03-13 22:00:00-07 │
└────────────────────────┘
postgres[2807421][1]=# SELECT ('2023-03-11 23:00:00-08'::timestamptz +
'23h'::interval) + '1day'::interval;
┌────────────────────────┐
│ ?column? │
├────────────────────────┤
│ 2023-03-13 23:00:00-07 │
└────────────────────────┘
Greetings,
Andres Freund
