an active time zone incorrectly changes interval math in UPDATE

[timgraham]

On CockroachDB master (a regression after cockroachdb/cockroach@917005b, perhaps):

> CREATE TABLE "example" ("id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid(), "start" timestamptz NOT NULL, "end" timestamptz NOT NULL);
> INSERT INTO "example" ("start", "end") VALUES ('2010-06-25T12:15:30.747000-05:00'::timestamptz, '2010-06-25T12:15:30.747000-05:00'::timestamptz);
> UPDATE "example" SET "end" = ("end" + '104166 days 57599.999999 seconds'::interval) WHERE true; 
> SELECT ("end" - "start") AS "delta" FROM "example";
2499999:59:59.999999

That's the expected result. Now setting a time zone before the update:

> TRUNCATE "example";
> INSERT INTO "example" ("start", "end") VALUES ('2010-06-25T12:15:30.747000-05:00'::timestamptz, '2010-06-25T12:15:30.747000-05:00'::timestamptz);
> SET TIME ZONE 'America/Chicago';
> UPDATE "example" SET "end" = ("end" + '104166 days 57599.999999 seconds'::interval) WHERE true;
> SELECT ("end" - "start") AS "delta" FROM "example";
         delta
------------------------
  2500000:59:59.999999

The result is expected to stay the same.

The failing Django test:

======================================================================
FAIL: test_datetime_subtraction_microseconds (expressions.tests.FTimeDeltaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 1247, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/tests/expressions/tests.py", line 1516, in test_datetime_subtraction_microseconds
    self.assertEqual(e.delta, delta)
AssertionError: datetime.timedelta(days=104166, seconds=61199, microseconds=999999) != datetime.timedelta(days=104166, seconds=57599, microseconds=999999)

[otan]

i believe this is because cockroach has different timezone data compared to postgres

example for cockroach

root@127.0.0.1:57773/defaultdb> select ('2010-06-25T12:15:30.747000-05:00'::timestamptz + '104166 days 57599.999999 seconds'::interval);
              ?column?
------------------------------------
  2295-09-06 04:15:30.746999-06:00
(1 row)

Time: 295µs

root@127.0.0.1:57882/defaultdb> select '2295-09-06 04:15:30.746999-06:00'::timestamptz;
            timestamptz
------------------------------------
  2295-09-06 04:15:30.746999-06:00
(1 row)

Time: 548µs

postgres:

otan=# select ('2010-06-25T12:15:30.747000-05:00'::timestamptz + '104166 days 57599.999999 seconds'::interval);
           ?column?
-------------------------------
 2295-09-06 04:15:30.746999-05
(1 row)

otan=# select '2295-09-06 04:15:30.746999-06:00'::timestamptz;
          timestamptz
-------------------------------
 2295-09-06 05:15:30.746999-05
(1 row)

notice the difference between -5 and -6.

my belief is that since postgres does not have timezone data that far in the future, it does not do the interval math correctly compared to cockroach, hence the result. note that for 180 days, this behaviour is the same between both cockroach and psql (and the interval is an hour longer):

cockroach

root@127.0.0.1:57882/defaultdb> select '2010-06-25T12:15:30.747000-05:00'::timestamptz + '180 days 57599.999999 seconds'::interval - '2010-06-25T12:15:30.747000-05:00'::timestamptz;
      ?column?
---------------------
  4336:59:59.999999
(1 row)

Time: 296µs

psql

otan=# select '2010-06-25T12:15:30.747000-05:00'::timestamptz + '180 days 57599.999999 seconds'::interval - '2010-06-25T12:15:30.747000-05:00'::timestamptz;
         ?column?
--------------------------
 180 days 16:59:59.999999
(1 row)

[otan]

interestingly enough, cockroach stops propagating DST after 2037:

root@127.0.0.1:57882/defaultdb> select '2038-09-06 04:15:30.746999-06:00'::timestamptz;
            timestamptz
------------------------------------
  2038-09-06 04:15:30.746999-06:00
(1 row)

Time: 321µs

root@127.0.0.1:57882/defaultdb> select '2037-09-06 04:15:30.746999-06:00'::timestamptz;
            timestamptz
------------------------------------
  2037-09-06 05:15:30.746999-05:00
(1 row)

Time: 399µs

[otan]

(edited - wrong)

[otan]

because this is language/os related (and not something that I can immediately address), i'm going to close this in favour of cockroachdb/cockroach#31978 and cockroachdb/cockroach#36864

[otan]

small edit: this is actually a golang issue -- filed cockroachdb/cockroach#45001