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.
>

Works for me on my time zone:

test=> select timeofday(), clock_timestamp();
timeofday | clock_timestamp
-------------------------------------+-------------------------------
Sat Mar 23 06:51:58.937533 2013 PDT | 2013-03-23 06:51:58.937579-07
(1 row)

test=> select timeofday()::timestamptz, clock_timestamp()::timestamptz;
timeofday | clock_timestamp
-------------------------------+-------------------------------
2013-03-23 06:52:14.547049-07 | 2013-03-23 06:52:14.547093-07

but not when I set to 'Europe/Moscow'

test=> set time zone 'Europe/Moscow';
SET
test=> select timeofday(), clock_timestamp();
timeofday | clock_timestamp
-------------------------------------+-------------------------------
Sat Mar 23 17:57:23.750469 2013 MSK | 2013-03-23 17:57:23.750495+04
(1 row)

test=> select timeofday()::timestamptz, clock_timestamp()::timestamptz;
timeofday | clock_timestamp
-------------------------------+-------------------------------
2013-03-23 18:57:31.529407+04 | 2013-03-23 17:57:31.529461+04
(1 row)

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.

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

>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com