Thread: different result between 8.3 and 8.5 (to_timestamp function)
Hello
our customer reports different result of to_timestamp function between
8.3 and 8.4
It looks like to_timestamp returns some strange timezone value
postgres=# select to_timestamp('00:00:00','HH24:MI:SS'); to_timestamp
─────────────────────────────────0001-01-01 00:00:00+00:57:44 BC
(1 row)
postgres=# select to_timestamp('1.2.2009 20:10:10','DD.MM.YYYY HH24:MI:SS'); to_timestamp
────────────────────────2009-02-01 20:10:10+01
(1 row)
8.3 returns +01
it
we missing 2 minutes, 15 second
any explanation?
Regards
Pavel Stehule
Pavel Stehule <pavel.stehule@gmail.com> writes:
> It looks like to_timestamp returns some strange timezone value
What timezone setting are you using? I'd bet a great deal that
+00:57:44 is what the Olsen database shows as the LMT offset for
your zone before standardized timezones were adopted.
regards, tom lane
2009/11/16 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> It looks like to_timestamp returns some strange timezone value > > What timezone setting are you using? I'd bet a great deal that > +00:57:44 is what the Olsen database shows as the LMT offset for > your zone before standardized timezones were adopted. > postgres=# select current_timestamp; now ───────────────────────────────2009-11-16 16:32:33.225416+01 (1 row) I am using CET. Pavel > regards, tom lane >
Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2009/11/16 Tom Lane <tgl@sss.pgh.pa.us>:
>> What timezone setting are you using? I'd bet a great deal that
>> +00:57:44 is what the Olsen database shows as the LMT offset for
>> your zone before standardized timezones were adopted.
> I am using CET.
"CET" covers a multitude of sins, but I suspect it's specifically
Europe/Prague:
# Zone NAME GMTOFF RULES FORMAT [UNTIL]
Zone Europe/Prague 0:57:44 - LMT 1850 0:57:44 - PMT 1891 Oct # Prague Mean Time
1:00 C-Eur CE%sT 1944 Sep 17 2:00s 1:00 Czech CE%sT 1979 1:00 EU CE%sT
So midnight local time corresponds to 00:57:44 GMT before 1891.
If you think this is wrong, use another zone, or take it up with
the zic folk.
regards, tom lane
2009/11/16 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> 2009/11/16 Tom Lane <tgl@sss.pgh.pa.us>: >>> What timezone setting are you using? I'd bet a great deal that >>> +00:57:44 is what the Olsen database shows as the LMT offset for >>> your zone before standardized timezones were adopted. > >> I am using CET. > > "CET" covers a multitude of sins, but I suspect it's specifically > Europe/Prague: > > # Zone NAME GMTOFF RULES FORMAT [UNTIL] > Zone Europe/Prague 0:57:44 - LMT 1850 > 0:57:44 - PMT 1891 Oct # Prague Mean Time > 1:00 C-Eur CE%sT 1944 Sep 17 2:00s > 1:00 Czech CE%sT 1979 > 1:00 EU CE%sT > > So midnight local time corresponds to 00:57:44 GMT before 1891. > If you think this is wrong, use another zone, or take it up with > the zic folk. > ok - I understand. Thank you Pavel > regards, tom lane >