Incorrect time zone handling before 1883

[timgraham]

Describe the problem

Time zone handling for dates before some time in 1883 or so (perhaps November 18, 1883 when standard time began) is incorrect (or at least differs from PostgreSQL).

To Reproduce

CREATE TABLE test (foo timestamptz);
INSERT INTO test VALUES ('1882-05-23T00:00:00-05:51'::timestamptz);
INSERT INTO test VALUES ('1883-05-23T00:00:00-05:51'::timestamptz);
INSERT INTO test VALUES ('1884-05-23T00:00:00-05:51'::timestamptz);
INSERT INTO test VALUES ('1885-05-23T00:00:00-05:51'::timestamptz);

> SELECT foo FROM test;
             foo
-----------------------------
  1882-05-23 05:51:00+00:00
  1883-05-23 05:51:00+00:00
  1884-05-23 05:51:00+00:00
  1885-05-23 05:51:00+00:00

> SET TIME ZONE 'America/Chicago';

> SELECT foo FROM test;
             foo
-----------------------------
  1882-05-23 00:00:24-05:50
  1883-05-23 00:00:24-05:50
  1884-05-22 23:51:00-06:00
  1885-05-22 23:51:00-06:00

Expected behavior

The first two results of the last query doesn't match PostgreSQL:

"1882-05-23 00:00:24-05:50:36"
"1883-05-23 00:00:24-05:50:36"
"1884-05-22 23:51:00-06"
"1885-05-22 23:51:00-06"

They include a seconds offset in the time zone so that the result is correct.

Environment:

• CockroachDB version 20.1.5

Additional context

I discovered this issue in the Django test suite:

======================================================================
FAIL: test_unicode_date (gis_tests.geoapp.test_regress.GeoRegressionTests)
Testing dates are converted properly, even on SpatiaLite. See #16408.
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/tests/gis_tests/geoapp/test_regress.py", line 56, in test_unicode_date
    self.assertEqual(founded, PennsylvaniaCity.objects.aggregate(Min('founded'))['founded__min'])
AssertionError: datetime.datetime(1857, 5, 23, 0, 0) != datetime.datetime(1857, 5, 23, 0, 0, 24)

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @rafiss (found keywords: Django)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}