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, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
