Re: Timezone bugs

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073