# Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?

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"
``````
• Also, a fine video about the insanity of time zones Feb 2, 2016 at 2:19
• The pivot point between BC and AD is year 1. It is either year 1 or year -1. It's just how years are named originally. Year 0 does not exist (or rather, is undefined since it's more of a definition problem rather than existential). Feb 2, 2016 at 6:32
• Remember way back during the 2000 celebrations when some pedantic people said that the second millennium technically starts in year 2001, not 2000? That's why. Years start at 1, not 0. And the year before year 1 is year 1 BC (ie. year -1) Feb 2, 2016 at 6:34
• @slebetman that depends on the calendar in use. The proleptic Gregorian has both a form that uses 0 as the year before 1 CE, and a form that places 1 BCE immediately before 1 CE (ISO 8601 supports both in having `0000` as a valid year value but not insisting on whether or not it is used). It's true that PostgreSQL uses the form with no year 0, but you can't state "years start a 1, not 0" as if its some sort of universal fact. It's easy to translate between them for e.g. astronomical data. (The third millennium still started on 2001 either way, since it remained the third millennium since 1 CE) Feb 2, 2016 at 15:02
• @JonHanna: Nobody was actually using any form of the proleptic Gregorian calendar at the time, though, so I think it's fair to privilege the Julian calendar here -- which does not have a year zero. Feb 2, 2016 at 18:15

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?