Re: timeofday() and clock_timestamp() produce different results when casting to timestamptz

On 03/23/2013 08:16 AM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com