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

Поиск
Список
Период
Сортировка
От Bryn Llewellyn
Тема i := t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
Дата
Msg-id 402F8EFB-3FE9-421A-B3BA-F736AB8E3ABB@yugabyte.com
обсуждение исходный текст
Ответы Re: i := t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
Re: i := t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
Список 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$;

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Hello - About how to install PgAdmin4 on Debian 10
Следующее
От: Jagmohan Kaintura
Дата:
Сообщение: PostgreSQL Views Where Clause Character Columns getting Typecasted to Text and Indexes not getting Used