Thread: BUG #5050: text to timestamp failure

The following bug has been logged online:

Bug reference:      5050
Logged by:          Aoyai Kouhei
Email address:      aoyagi.kouhei@gmail.com
PostgreSQL version: 8.4.1
Operating system:   MacOS 10.6.1
Description:        text to timestamp failure
Details:

select '0001-01-01'::timestamp with time zone;
-> "0001-01-01 00:00:00+09:18:59"

...

select '1887-01-01'::timestamp with time zone;
-> "1887-01-01 00:00:00+09:18:59"

select '1888-01-01'::timestamp with time zone;
-> "1888-01-01 00:00:00+09"

select '1889-01-01'::timestamp with time zone;
-> "1889-01-01 00:00:00+09"

...

"Aoyai Kouhei" <aoyagi.kouhei@gmail.com> writes:
> select '0001-01-01'::timestamp with time zone;
> -> "0001-01-01 00:00:00+09:18:59"

> select '1887-01-01'::timestamp with time zone;
> -> "1887-01-01 00:00:00+09:18:59"

You did not say what timezone setting you are using, but in some of them
this is not wrong.

            regards, tom lane

Thanks reply.
My timezone is Asia/Tokyo. utc_offset_interval is 09:00:00.

2009/9/13 Tom Lane <tgl@sss.pgh.pa.us>

> "Aoyai Kouhei" <aoyagi.kouhei@gmail.com> writes:
> > select '0001-01-01'::timestamp with time zone;
> > -> "0001-01-01 00:00:00+09:18:59"
>
> > select '1887-01-01'::timestamp with time zone;
> > -> "1887-01-01 00:00:00+09:18:59"
>
> You did not say what timezone setting you are using, but in some of them
> this is not wrong.
>
>                        regards, tom lane
>

aoyogi kouhei <aoyagi.kouhei@gmail.com> writes:
>> You did not say what timezone setting you are using, but in some of them
>> this is not wrong.

> My timezone is Asia/Tokyo. utc_offset_interval is 09:00:00.

According to the Olson timezone database, Tokyo observed local mean
solar time until the end of 1887:

--- snip ---
# From Hideyuki Suzuki (1998-11-09):
# 'Tokyo' usually stands for the former location of Tokyo Astronomical
# Observatory: E 139 44' 40".90 (9h 18m 58s.727), N 35 39' 16".0.
# This data is from 'Rika Nenpyou (Chronological Scientific Tables) 1996'
# edited by National Astronomical Observatory of Japan....
# JST (Japan Standard Time) has been used since 1888-01-01 00:00 (JST).
# The law is enacted on 1886-07-07.

# From Hideyuki Suzuki (1998-11-16):
# The ordinance No. 51 (1886) established "standard time" in Japan,
# which stands for the time on E 135 degree.
# In the ordinance No. 167 (1895), "standard time" was renamed to "central
# standard time".  And the same ordinance also established "western standard
# time", which stands for the time on E 120 degree....  But "western standard
# time" was abolished in the ordinance No. 529 (1937).  In the ordinance No.
# 167, there is no mention regarding for what place western standard time is
# standard....
#
# I wrote "ordinance" above, but I don't know how to translate.
# In Japanese it's "chokurei", which means ordinance from emperor.

# Shanks & Pottenger claim JST in use since 1896, and that a few
# places (e.g. Ishigaki) use +0800; go with Suzuki.  Guess that all
# ordinances took effect on Jan 1.

# Zone    NAME        GMTOFF    RULES    FORMAT    [UNTIL]
Zone    Asia/Tokyo    9:18:59    -    LMT    1887 Dec 31 15:00u
            9:00    -    JST    1896
            9:00    -    CJT    1938
            9:00    Japan    J%sT
--- snip ---

So the 9:18:59 offset from Greenwich is correct for local midnight
before that date.  If you don't like it, feel free to change your
local copy of that timezone data file ... or, if you can provide
historical evidence that this is not correct, please submit that
to the Olson crew.

            regards, tom lane