Re: Timezone bugs - Mailing list pgsql-hackers

My guess is that is this commit that is causing the problem:
revision 1.110date: 2005/06/15 00:34:08;  author: momjian;  state: Exp;  lines: +48 -40This patch makes it possible to
usethe full set of timezones when doing"AT TIME ZONE", and not just the shorlist previously available.
Forexample:SELECTCURRENT_TIMESTAMP AT TIME ZONE 'Europe/London';works fine now. It will also obey whatever DST rules
werein effect atjust that date, which the previous implementation did not.It also supports the AT TIME ZONE on the
timetzdatatype. The wholehandling of DST is a bit bogus there, so I chose to make it use whateverDST rules are in
effectat the time of executig the query. not sure ifanybody is actuallyi *using* timetz though, it seems
prettyunpredictablejust because of this...
 
Magnus Hagander

I will research it tomorrow and report back.

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

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)
> 
> 
> *** 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)
> 
> 
> select '05:00'::time at time zone 'Canada/Pacific';
>   timezone   
> -------------
>  22:00:00-07
> (1 row)
> 
> Wrong time.
> 
> 
> 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
shouldADD 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
thetimetz 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