Skip site navigation (1) Skip section navigation (2)

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

From: "" <tcook(at)blackducksoftware(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4377: casting result of timeofday() to timestamp fails in some timezones
Date: 2008-08-26 20:07:45
Message-ID: 200808262007.m7QK7j0Y011359@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4377
Logged by:          
Email address:      tcook(at)blackducksoftware(dot)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)

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-08-26 23:38:01
Subject: Re: BUG #4377: casting result of timeofday() to timestamp fails in some timezones
Previous:From: Pavel StehuleDate: 2008-08-26 19:37:34
Subject: Re: non-deterministic error related to MIN/MAX optimization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group