i := t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: i := t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
Date: 2021-03-26 03:28:03
Message-ID: 402F8EFB-3FE9-421A-B3BA-F736AB8E3ABB@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Given these, where the timestamptz values span the start of US/Pacific DST:

t0 timestamptz := ...;
t1 timestamptz := ...;
i_by_subtraction interval second := t1 - t0;

then this expression tests FALSE:

t0 + i_by_subtraction i = t1

There's more. We see that while the two "interval second” values '1 day 01:00:00' and '25 hours' test as equal, the results of adding each to the same timestamptz value are different.

PLEASE STATE THE RULES THAT ALLOW THE BEHAVIOR OF THE BLOCK TO BE PREDICTED.

(I searched the doc but found nothing.)

------------------------------------------------------------------------------------------
— Self-contained test case. Finishes silently in PG 13.2 showing that all assertions hold.

do $body$
declare
t0_text constant text := '2021-03-13 20:00:00';
t_dst constant text := '2021-03-14 02:00:00';
t1_text constant text := '2021-03-14 22:00:00';
t_surprise constant text := '2021-03-14 21:00:00';

-- i_expected because we spring forward to US DST between t0_text and t1_text.
i_expected constant interval second := '1 day 01:00:00';
i_25_hours constant interval second := '25 hours';

t0 timestamptz;
t1 timestamptz;
i_by_subtraction interval second;
begin
assert t0_text < t_dst and t_dst < t1_text, 'assert #1 failed';
assert t_surprise <> t1_text, 'assert #2 failed';

------------------------------------------------------------
-- Look!
assert i_expected = i_25_hours, 'assert #3 failed';
------------------------------------------------------------

set time zone 'US/Pacific';
t0 := t0_text::timestamptz;
t1 := t1_text::timestamptz;

assert t0::text = t0_text||'-08', 'assert #4 failed';
assert t1::text = t1_text||'-07', 'assert #5 failed'; --<< compare

i_by_subtraction := t1 - t0;
assert i_by_subtraction = i_expected, 'assert #6 failed';

------------------------------------------------------------
-- HERE IS THE PARADOX.
t1 := t0 + i_expected;
assert t1::text = t_surprise||'-07', 'assert #7 failed';

-- Meanwhile...
t1 := t0 + i_25_hours;
assert t1::text = t1_text||'-07', 'assert #8 failed';--<< compare (same)
------------------------------------------------------------
end;
$body$;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jagmohan Kaintura 2021-03-26 04:31:45 PostgreSQL Views Where Clause Character Columns getting Typecasted to Text and Indexes not getting Used
Previous Message Adrian Klaver 2021-03-25 22:14:30 Re: Hello - About how to install PgAdmin4 on Debian 10