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)