Interval math across dst works differently from PostgreSQL (and other databases)

[timgraham]

(Note that daylight saving time "fall back" is Nov. 7 in 2010.)

On PostgreSQL (no time zone active):

> CREATE TABLE "example" ("id" serial NOT NULL PRIMARY KEY, "a" timestamp with time zone NOT NULL); 
> INSERT INTO "example" ("a") VALUES ('2010-11-03T12:42:10.234567-05:00'::timestamp); 
> SELECT * FROM "example";
"2010-11-03 12:42:10.234567-04"

> UPDATE "example" SET "a" = ("a" + '15 days 0.000000 seconds'::interval);
> SELECT "a" FROM "example";
"2010-11-18 12:42:10.234567-05"
> TRUNCATE "example";

Time zone changes from -04 to -05 but hour remains the same at 12.

With time zone active:

> SET TIME ZONE "America/Chicago";
> INSERT INTO "example" ("a") VALUES ('2010-11-03T12:42:10.234567-05:00'::timestamp); 
> SELECT * FROM "example";
"2010-11-03 12:42:10.234567-05"

> UPDATE "example" SET "a" = ("a" + '15 days 0.000000 seconds'::interval);
> SELECT "a" FROM "example";
"2010-11-18 12:42:10.234567-06"

Time zone changes from -5 to -6 but no hour change.

On cockroachdb:

> CREATE TABLE "example" ("id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid(), "a" timestamptz NOT NULL);
> INSERT INTO "example" ("a") VALUES ('2010-11-03T12:42:10.234567-05:00'::timestamptz);
> SELECT * FROM "example";
          id         |                a                  
+--------------------+----------------------------------+
  497354447372025857 | 2010-11-03 17:42:10.234567+00:00  
> UPDATE "example" SET "a" = ("a" + '15 days 0.000000 seconds'::interval) WHERE id=497354447372025857;
> SELECT * FROM "example";
          id         |                a                  
+--------------------+----------------------------------+
  497354447372025857 | 2010-11-18 17:42:10.234567+00:00

> TRUNCATE "example";

Fine, but with a time zone active...

> SET TIME ZONE "America/Chicago";
> INSERT INTO "example" ("a") VALUES ('2010-11-03T12:42:10.234567-05:00'::timestamptz);
> SELECT * FROM "example";
          id         |                a                  
+--------------------+----------------------------------+
  497355106025603073 | 2010-11-03 12:42:10.234567-05:00 

>  UPDATE "example" SET "a" = ("a" + '15 days 0.000000 seconds'::interval) WHERE id=497355106025603073;

> SELECT * FROM "example";
          id         |                a                  
+--------------------+----------------------------------+
  497355106025603073 | 2010-11-18 11:42:10.234567-06:00  

Time zone changes from -5 to -6 AND hour changes from 12 to 11.

Failing Django test:

======================================================================
FAIL: test_delta_update (expressions.tests.FTimeDeltaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 59, in testPartExecutor
    yield
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 615, in run
    testMethod()
  File "/home/tim/code/django/tests/expressions/tests.py", line 1232, in test_delta_update
    self.assertEqual(expected_ends, new_ends)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 839, in assertEqual
    assertion_func(first, second, msg=msg)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 1045, in assertListEqual
    self.assertSequenceEqual(list1, list2, msg, seq_type=list)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 1027, in assertSequenceEqual
    self.fail(msg)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 680, in fail
    raise self.failureException(msg)
AssertionError: Lists differ: [date[198 chars], 10, 14, 9, 24, 15), datetime.datetime(2011, 2, 1, 9, 24, 15)] != [date[198 chars], 10, 14, 9, 24, 15), datetime.datetime(2011, 2, 1, 8, 24, 15)]

First differing element 5:
datetime.datetime(2011, 2, 1, 9, 24, 15)
datetime.datetime(2011, 2, 1, 8, 24, 15)

  [datetime.datetime(2010, 10, 4, 9, 24, 15),
   datetime.datetime(2010, 10, 5, 9, 24, 15, 253000),
   datetime.datetime(2010, 10, 4, 9, 24, 59),
   datetime.datetime(2010, 10, 9, 6, 32, 15),
   datetime.datetime(2010, 10, 14, 9, 24, 15),
-  datetime.datetime(2011, 2, 1, 9, 24, 15)]
?                                ^

+  datetime.datetime(2011, 2, 1, 8, 24, 15)]
?                                ^

[timgraham]

@otan, this still affects the latest CockroachDB build and might be in your area of expertise.

[otan]

I think in this case it is postgres which is buggy. Chicago is never -4, is it?

[timgraham]

Sorry, my instructions were unclear -- the -4 happens when no time zone is active. I added the "With time zone active" section for PostgreSQL to the issue description. I believe the behavior is correct considering that the test also works on SQLite, MySQL, Oracle, etc.

[otan]

Nah I misread, I gotcha!

[otan]

urgh, #44792 doesn't solve the case where it is persisted in the DB o_o"