Thread: timeofday() and clock_timestamp() produce different results when casting to timestamptz
timeofday() and clock_timestamp() produce different results when casting to timestamptz
From
Sergey Konoplev
Date:
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.
Timezone is Europe/Moscow. Version information:
smoking_test=# select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp
Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com
Re: timeofday() and clock_timestamp() produce different results when casting to timestamptz
From
Adrian Klaver
Date:
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.klaver@gmail.com
Re: timeofday() and clock_timestamp() produce different results when casting to timestamptz
From
Adrian Klaver
Date:
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
Re: 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: 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