In Postgres 9.5, I was surprised to see the result seen below while experimenting with year 0001
(no year zero 0000
).
Offset of -07:52:58
?
Some example code. Note that I mixed use of TIMESTAMP WITH TIME ZONE
and TIMESTAMP WITHOUT TIME ZONE
, so read carefully.
SET TIME ZONE 'America/Los_Angeles' ;
SELECT (TIMESTAMP WITH TIME ZONE '2015-01-01 00:00:00.0',
TIMESTAMP WITH TIME ZONE '0001-01-01 00:00:00.0Z',
TIMESTAMP WITHOUT TIME ZONE '0001-01-01 00:00:00.0Z') ;
("2015-01-01 00:00:00-08","0001-12-31 16:07:02-07:52:58 BC","0001-01-01 00:00:00")
I am surprised by that second value: 0001-12-31 16:07:02-07:52:58 BC
. I understand that we must go backwards eight hours as America/Los_Angeles
is eight hours behind UTC with an offset of -08:00
. But instead of -08:00
the offset is -07:52:58
. Why?
No Problem Under UTC
No such issue when entering data under UTC.
SET TIME ZONE 'UTC' ;
SELECT (TIMESTAMP WITH TIME ZONE '2015-01-01 00:00:00.0',
TIMESTAMP WITH TIME ZONE '0001-01-01 00:00:00.0Z',
TIMESTAMP WITHOUT TIME ZONE '0001-01-01 00:00:00.0Z');
("2015-01-01 00:00:00+00","0001-01-01 00:00:00+00","0001-01-01 00:00:00")
No Year Zero
By the way, the date portion seems to be correct. It seems there is no year 0000
, that being the pivot point between the “BC” and “AD” eras. Take the first moment of year 0001, subtract an hour, and you get the year 0001 BC
– so no year zero.
SET TIME ZONE 'UTC' ;
INSERT INTO moment_ -- TIMESTAMP WITH TIME ZONE.
VALUES ( TIMESTAMP '0001-01-01 00:00:00.0Z' - INTERVAL '1 hour' ) ;
SET TIME ZONE 'UTC' ;
TABLE moment_ ;
The result is the year 0001 BC
, so we jump from 0001
to 0001 BC
; no year zero 0000
.
"0001-12-31 23:00:00+00 BC"
Best Answer
On 18th of November, 1883 at 12:00 (new time), standard time was adopted by the American railroads.
This means that before that time, Los Angeles used actual local time, based on mean solar time. After that, it was moved to its local time zone, which, being an integral offset of hours from the Greenwich Mean Time, was slightly different from the previous time.
Want to know more?
Download the tzdata Timezone Database from IANA: Time zones.
Inside, you'll find the definitions of the (many) timezones, which have lots of variations over time, along with lots of comments detailing what changes where made and when. It's entertaining reading!
Wikipedia has also some interesting facts, in the Wikipedia: Time zone page, regarding the 1883, November 18 change:
Also note that this is not specific to Postgresql. This is valid for any software or operating system that uses the tzdata database (though of course many will be limited to dates either post 1970 or post 1901, so 1883 is beyond reach, but there are many, many other adjustments all over the place at different times).