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$; |
