Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux