Thread: Re: [GENERAL] timeofday() and clock_timestamp() produce different results when casting to timestamptz
Re: [GENERAL] timeofday() and clock_timestamp() produce different results when casting to timestamptz
From
Tom Lane
Date:
Adrian Klaver <adrian.klaver@gmail.com> writes:
> Seems the time zone info still thinks Moscow time is being setting
> forward an hour for DST when in fact the time remains constant through
> the year.
I think the discrepancy is between this in timezone/data/europe:
Zone Europe/Moscow 2:30:20 - LMT 1880
2:30 - MMT 1916 Jul 3 # Moscow Mean Time
2:30:48 Russia %s 1919 Jul 1 2:00
3:00 Russia MSK/MSD 1922 Oct
2:00 - EET 1930 Jun 21
3:00 Russia MSK/MSD 1991 Mar 31 2:00s
2:00 Russia EE%sT 1992 Jan 19 2:00s
3:00 Russia MSK/MSD 2011 Mar 27 2:00s
4:00 - MSK
and this in timezone/tznames/Default:
MSD 14400 D # Moscow Daylight Time
# (Europe/Moscow)
MSK 10800 # Moscow Time
# (Europe/Moscow)
We really need to figure out a way to update the tznames data
automatically, or at least notice when it's become inconsistent with
the underlying Olson database.
regards, tom lane
Re: [GENERAL] timeofday() and clock_timestamp() produce different results when casting to timestamptz
From
Adrian Klaver
Date:
On 03/23/2013 08:16 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@gmail.com> writes:
>> Seems the time zone info still thinks Moscow time is being setting
>> forward an hour for DST when in fact the time remains constant through
>> the year.
>
> I think the discrepancy is between this in timezone/data/europe:
>
> Zone Europe/Moscow 2:30:20 - LMT 1880
> 2:30 - MMT 1916 Jul 3 # Moscow Mean Time
> 2:30:48 Russia %s 1919 Jul 1 2:00
> 3:00 Russia MSK/MSD 1922 Oct
> 2:00 - EET 1930 Jun 21
> 3:00 Russia MSK/MSD 1991 Mar 31 2:00s
> 2:00 Russia EE%sT 1992 Jan 19 2:00s
> 3:00 Russia MSK/MSD 2011 Mar 27 2:00s
> 4:00 - MSK
>
> and this in timezone/tznames/Default:
>
> MSD 14400 D # Moscow Daylight Time
> # (Europe/Moscow)
> MSK 10800 # Moscow Time
> # (Europe/Moscow)
>
> We really need to figure out a way to update the tznames data
> automatically, or at least notice when it's become inconsistent with
> the underlying Olson database.
So I temporary fix would be to go into /share/timezonesets/Default
and change :
MSK 10800 # Moscow Time
to
MSK 14400 # Moscow Time
and then you get:
test=> set time zone 'Europe/Moscow';
SET
test=> select timeofday(), clock_timestamp();
timeofday | clock_timestamp
-------------------------------------+-------------------------------
Sun Mar 24 03:50:45.066537 2013 MSK | 2013-03-24 03:50:45.066582+04
(1 row)
test=> select timeofday()::timestamptz, clock_timestamp()::timestamptz;
timeofday | clock_timestamp
-------------------------------+-------------------------------
2013-03-24 03:50:52.485092+04 | 2013-03-24 03:50:52.485188+04
(1 row)
>
> regards, tom lane
>
>
--
Adrian Klaver
adrian.klaver@gmail.com