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

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