Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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"
share|improve this question
    
Also, a fine video about the insanity of time zones – billinkc Feb 2 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). – slebetman Feb 2 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) – slebetman Feb 2 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) – Jon Hanna Feb 2 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. – Kevin Feb 2 at 18:15
up vote 20 down vote accepted

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:

Railway time
...
Timekeeping on the American railroads in the mid-19th century was somewhat confused. Each railroad used its own standard time, usually based on the local time of its headquarters or most important terminus, and the railroad's train schedules were published using its own time. Some junctions served by several railroads had a clock for each railroad, each showing a different time.
... Dowd's system was never accepted by American railroads. Instead, U.S. and Canadian railroads implemented a version proposed by William F. Allen, the editor of the Traveler's Official Railway Guide. The borders of its time zones ran through railroad stations, often in major cities. For example, the border between its Eastern and Central time zones ran through Detroit, Buffalo, Pittsburgh, Atlanta, and Charleston. It was inaugurated on Sunday, November 18, 1883, also called "The Day of Two Noons", when each railroad station clock was reset as standard-time noon was reached within each time zone. The zones were named Intercolonial, Eastern, Central, Mountain, and Pacific. ...

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.