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

Re: Timezone bugs

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Kevin McArthur <postgresql-list(at)stormtide(dot)ca>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Timezone bugs
Date: 2005-07-22 05:00:45
Message-ID: 200507220500.j6M50jK07083@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2005-07-22 05:06:38
Subject: Re: Imprecision of DAYS_PER_MONTH
Previous:From: Thomas F. O'ConnellDate: 2005-07-22 04:51:41
Subject: Re: bgwriter, inherited temp tables TODO items?

pgsql-patches by date

Next:From: Andrew DunstanDate: 2005-07-22 05:27:41
Subject: Re: COPY FROM performance improvements
Previous:From: Luke LonerganDate: 2005-07-22 04:19:04
Subject: Re: COPY FROM performance improvements

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