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

On 03/23/2013 12:37 AM, Sergey Konoplev wrote:
> Hi all,
>
> Today after upgrading to 9.2 from 9.0 I faced a very odd
> incompatibility that seems to me as a bug:
>
> smoking_test=# select timeofday(), clock_timestamp();
>                timeofday              |        clock_timestamp
> -------------------------------------+-------------------------------
>   Sat Mar 23 11:20:54.023796 2013 MSK | 2013-03-23 11:20:54.023815+04
>
> smoking_test=# select timeofday()::timestamptz, clock_timestamp()::timestamptz;
>             timeofday           |        clock_timestamp
> -------------------------------+-------------------------------
>   2013-03-23 12:21:03.995653+04 | 2013-03-23 11:21:03.995695+04
>
> As you can see after casting timeofday() to timestamp with time zone
> it adds one hour to the timestamp when clock_timestamp() behaves
> normally.
>

Sent my previous message too soon, to add:

Postgres seems to be aware of the DST status for Europe/Moscow(MSK):

test=> select * from pg_timezone_names where abbrev='MSK';


      name      | abbrev | utc_offset | is_dst


---------------+--------+------------+--------


  Europe/Moscow | MSK    | 04:00:00   | f


  W-SU          | MSK    | 04:00:00   | f


test=> select * from pg_timezone_names where name='Europe/Moscow';
      name      | abbrev | utc_offset | is_dst
---------------+--------+------------+--------
  Europe/Moscow | MSK    | 04:00:00   | f
(1 row)

Would seem to mean the zoneinfo database is incorrect.

>
> --
> Sergey Konoplev
> Database and Software Architect
> http://www.linkedin.com/in/grayhemp
>


--
Adrian Klaver
adrian.klaver@gmail.com