Re: Timezone bugs - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: Timezone bugs
Date
Msg-id 200507220500.j6M50jK07083@candle.pha.pa.us
Whole thread Raw
In response to Timezone bugs  ("Kevin McArthur" <postgresql-list@stormtide.ca>)
List pgsql-hackers
Kevin McArthur wrote:
> There appear to be several bugs in the at time zone patch recently applied.
> 
> 
> show timezone;
>  TimeZone 
> ----------
>  UTC
> (1 row)
> 
> select now();
>               now              
> -------------------------------
>  2005-07-20 23:38:57.981128+00
> (1 row)
> 
> 
> *** WORKS ***
> 
> 
>  select CURRENT_DATE + '05:00'::time at time zone 'Canada/Pacific';
>         ?column?        
> ------------------------
>  2005-07-21 05:00:00+00
> (1 row)

OK.

> *** BROKEN ***
> 
> 
> select (CURRENT_DATE + '05:00'::time)::timestamp at time zone 'Canada/Pacific';
>         timezone        
> ------------------------
>  2005-07-19 22:00:00+00
> (1 row)
> 
> Wrong date _and_ time. (Ive not even got a theory about how the date gets wrong here)

What is happening here is that 2005-07-20 05:00:00 is being cast back 7
hours (Canada/Pacific offset), and that is 22:00 of the previous day.

> 
> 
> select '05:00'::time at time zone 'Canada/Pacific';
>   timezone   
> -------------
>  22:00:00-07
> (1 row)
> 
> Wrong time.

Well, again 5am GMT is 22:00 Canada/Pacific, no?

---------------------------------------------------------------------------


> I think there's something fishy going on with internal casts.
> 
> if time has no timezone it should not imply a cast to timetz using the
> local timezone, instead time to at time zone should ADD timezone
> information to the datatype to result in a timetz though that may
> require the use of something like select '05:00'::time at time zone
> 'Canada/Pacific' on CURRENT_DATE::date to do properly.
> 
> Suggested resolution would be to allow the actual storage of named
> timezone descriptions like 'Canada/Pacfiic' within the timetz datatype
> natively instead of converting to utc for storage (which is logically
> invalid).
> 
> The application of this whole problem is for a VoIP network to be able
> to handle evening and weekend calling based on cities. Evening being
> after 6pm in vacouver per se. Current and historical processing of the
> time zone data is also a requirement.
> 
> Hope that helps.
> 
> Kevin McArthur
> 
> Director
> StormTide Digital Studios Inc.
> 
> 
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


pgsql-hackers by date:

Previous
From: "Thomas F. O'Connell"
Date:
Subject: Re: bgwriter, inherited temp tables TODO items?
Next
From: Bruce Momjian
Date:
Subject: Re: Imprecision of DAYS_PER_MONTH