Thread: timezones BCE

timezones BCE

From
Dave Cramer
Date:
Can someone help me understand

select '0101-01-01'::timestamptz;
      timestamptz
------------------------
 0101-01-01 00:00:00+00
(1 row)

test=# set timezone to 'America/Toronto';
SET
test=# select '0101-01-01'::timestamptz;
         timestamptz
------------------------------
 0101-01-01 00:00:00-05:17:32
(1 row)

select 'now()'::timestamptz;
          timestamptz
-------------------------------
 2022-04-13 12:31:57.271967-04
(1 row)

Specifically why the -05:17:32


Dave Cramer

Re: timezones BCE

From
chap@anastigmatix.net
Date:
On 2022-04-13 12:33, Dave Cramer wrote:
> test=# set timezone to 'America/Toronto';
> SET
> test=# select '0101-01-01'::timestamptz;
>          timestamptz
> ------------------------------
>  0101-01-01 00:00:00-05:17:32
> 
> Specifically why the -05:17:32

Timezones were regularized into their (typically hour-wide) chunks
during a period around the late nineteenth century IIRC.

If you decompile the zoneinfo database to look at America/Toronto,
you will probably find an entry for dates earlier than when the
regularized zones were established there, and that entry will have
an offset reflecting Toronto's actual longitude.

Regards,
-Chap



Re: timezones BCE

From
Tom Lane
Date:
chap@anastigmatix.net writes:
> On 2022-04-13 12:33, Dave Cramer wrote:
>> Specifically why the -05:17:32

> Timezones were regularized into their (typically hour-wide) chunks
> during a period around the late nineteenth century IIRC.

> If you decompile the zoneinfo database to look at America/Toronto,
> you will probably find an entry for dates earlier than when the
> regularized zones were established there, and that entry will have
> an offset reflecting Toronto's actual longitude.

Yeah, you'll see these weird offsets in just about every zone for dates
earlier than the late 1800s.  I've got my doubts about how useful it is
to do that, but that's the policy the tzdb guys have.

At one point I was considering whether we could project the oldest
recorded "standard time" offset backwards instead of believing the LMT
offsets.  This would confuse many fewer people, and it's no less
logically defensible than applying the Gregorian calendar to years
centuries before Pope Gregory was born.  But I fear that horse may
have left the barn already --- changing this behavior would have
its own downsides, and I do not think any other tzdb consumers do it.

            regards, tom lane



Re: timezones BCE

From
Dave Cramer
Date:


On Wed, 13 Apr 2022 at 14:10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
chap@anastigmatix.net writes:
> On 2022-04-13 12:33, Dave Cramer wrote:
>> Specifically why the -05:17:32

> Timezones were regularized into their (typically hour-wide) chunks
> during a period around the late nineteenth century IIRC.

> If you decompile the zoneinfo database to look at America/Toronto,
> you will probably find an entry for dates earlier than when the
> regularized zones were established there, and that entry will have
> an offset reflecting Toronto's actual longitude.

Yeah, you'll see these weird offsets in just about every zone for dates
earlier than the late 1800s.  I've got my doubts about how useful it is
to do that, but that's the policy the tzdb guys have.

At one point I was considering whether we could project the oldest
recorded "standard time" offset backwards instead of believing the LMT
offsets.  This would confuse many fewer people, and it's no less
logically defensible than applying the Gregorian calendar to years
centuries before Pope Gregory was born.  But I fear that horse may
have left the barn already --- changing this behavior would have
its own downsides, and I do not think any other tzdb consumers do it.
 
Oh please don't do something bespoke. I'm trying to make this work with the JDBC driver.
So it has to be at least compatible with other libraries.

Dave

Re: timezones BCE

From
chap@anastigmatix.net
Date:
On 2022-04-13 14:13, Dave Cramer wrote:
> 
> Oh please don't do something bespoke. I'm trying to make this work with 
> the
> JDBC driver.
> So it has to be at least compatible with other libraries.

Looks like Java agrees with the offset, prior to Toronto's 1895 adoption
of the hour-wide zone:

jshell> java.time.ZoneId.of("America/Toronto").
    ...> getRules().
    ...> nextTransition(java.time.Instant.parse("0101-01-01T00:00:00Z"))
$1 ==> Transition[Gap at 1895-01-01T00:00-05:17:32 to -05:00]

Regards,
-Chap