Thread: BUG #4377: casting result of timeofday() to timestamp fails in some timezones

The following bug has been logged online:

Bug reference:      4377
Logged by:
Email address:      tcook@blackducksoftware.com
PostgreSQL version: 8.3.3
Operating system:   Fedora 7
Description:        casting result of timeofday() to timestamp fails in some
timezones
Details:

It looks like pg_timezone_abbrevs is missing some entries?


test=> select version();
                                                 version
----------------------------------------------------------------------------
------------------------------
 PostgreSQL 8.3.3 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20070925 (Red Hat 4.1.2-27)
(1 row)

test=> show timezone;
  TimeZone
------------
 US/Eastern
(1 row)

test=> select timeofday();
              timeofday
-------------------------------------
 Tue Aug 26 16:01:03.792786 2008 EDT
(1 row)

test=> select timeofday()::timestamp;
         timeofday
----------------------------
 2008-08-26 16:01:17.513467
(1 row)

test=> set timezone = 'Israel';
SET
test=> select timeofday();
              timeofday
-------------------------------------
 Tue Aug 26 23:01:52.602051 2008 IDT
(1 row)

test=> select timeofday()::timestamp;
ERROR:  invalid input syntax for type timestamp: "Tue Aug 26 23:01:55.409996
2008 IDT"
test=>
test=> select * from pg_timezone_names where abbrev ~* 'IDT' ;
         name         | abbrev | utc_offset | is_dst
----------------------+--------+------------+--------
 Asia/Jerusalem       | IDT    | 03:00:00   | t
 Asia/Tel_Aviv        | IDT    | 03:00:00   | t
 posix/Asia/Jerusalem | IDT    | 03:00:00   | t
 posix/Asia/Tel_Aviv  | IDT    | 03:00:00   | t
 posix/Israel         | IDT    | 03:00:00   | t
 right/Asia/Jerusalem | IDT    | 03:00:00   | t
 right/Asia/Tel_Aviv  | IDT    | 03:00:00   | t
 right/Israel         | IDT    | 03:00:00   | t
 Israel               | IDT    | 03:00:00   | t
(9 rows)

test=>
test=> select * from pg_timezone_abbrevs where abbrev ~* 'IDT' ;
 abbrev | utc_offset | is_dst
--------+------------+--------
(0 rows)

"" <tcook@blackducksoftware.com> writes:
> It looks like pg_timezone_abbrevs is missing some entries?

Well, it's intentional that the view not contain *every* abbrev,
since there are conflicts.  But I have noticed that the files under
src/timezone/tznames/ seem to be missing some abbreviations that are
in fact known in the zic database.  Somebody ought to go through that
info again sometime, first to find any missing abbrevs and second to
see if there are more that we could safely promote into the Default
list.  It sounds like a mind-numbingly tedious task though :-(

In the meantime, why aren't you just using clock_timestamp()?
timeofday() is deprecated.

            regards, tom lane

Tom Lane wrote:

> Well, it's intentional that the view not contain *every* abbrev,
> since there are conflicts.  But I have noticed that the files under
> src/timezone/tznames/ seem to be missing some abbreviations that are
> in fact known in the zic database.  Somebody ought to go through that
> info again sometime, first to find any missing abbrevs and second to
> see if there are more that we could safely promote into the Default
> list.  It sounds like a mind-numbingly tedious task though :-(

Added to TODO:

{{TodoItemEasy
|Revise the src/timezone/tznames abbreviation files:
|
* to add missing abbreviations
* to find abbreviations that can be safely promoted to the Default list
* {{messageLink|7867.1219793881@sss.pgh.pa.us|BUG #4377: casting result of timeofday() to timestamp fails in some
timezones}}}}


--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> It sounds like a mind-numbingly tedious task though :-(

> Added to TODO:

> {{TodoItemEasy
> |Revise the src/timezone/tznames abbreviation files:

BTW, it just occurred to me to wonder whether we couldn't modify the
"zic" application to extract a list of all the abbrevs and their GMT
offsets from the tzdata source files.  You'd still have a lot of manual
effort to classify the results, but this would eliminate one big part
of the drudgery...

            regards, tom lane

Tom Lane wrote:
>
> In the meantime, why aren't you just using clock_timestamp()?
> timeofday() is deprecated.

I am using clock_timestamp() now.  The use of timeofday() was from the era
when 8.0 was hot stuff.

BTW, the word "deprecated" does not appear on the docs page where timeofday()
is listed (http://www.postgresql.org/docs/8.3/static/functions-datetime.html),
and there doesn't seem to be anything in the context of the 3 occurrences of
"timeofday" that would imply it is deprecated.

-- todd

Todd A. Cook wrote:
> Tom Lane wrote:
> >
> > In the meantime, why aren't you just using clock_timestamp()?
> > timeofday() is deprecated.
>
> I am using clock_timestamp() now.  The use of timeofday() was from the era
> when 8.0 was hot stuff.
>
> BTW, the word "deprecated" does not appear on the docs page where timeofday()
> is listed (http://www.postgresql.org/docs/8.3/static/functions-datetime.html),
> and there doesn't seem to be anything in the context of the 3 occurrences of
> "timeofday" that would imply it is deprecated.

Uh, we will probably never remove timeofday() but the new functions are
clearer.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +