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

Re: [HACKERS] Timezone bugs

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Cc: andrew(at)supernews(dot)com
Subject: Re: [HACKERS] Timezone bugs
Date: 2005-07-23 14:25:42
Message-ID: 200507231425.j6NEPgT05390@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-patches
Andrew pointed out that the current fix didn't handle dates that were
near daylight savings time boudaries.  This handles it properly, e.g.

	test=> select '2005-04-03 04:00:00'::timestamp at time zone
	'America/Los_Angeles';
	        timezone
	------------------------
	 2005-04-03 07:00:00-04
	(1 row)

Patch attached and applied.  The new fix is cleaner too.

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

pgman wrote:
> 
> OK, tricky, but fixed --- patch attached and applied, with documentation
> updates.  Here is the test query:
> 
> 	test=> select (CURRENT_DATE + '05:00'::time)::timestamp at time zone
> 	'Canada/Pacific';
> 	        timezone
> 	------------------------
> 	 2005-07-22 08:00:00-04
> 	(1 row)
> 	
> I tested a bunch of others too, like:
> 
> 	test=> select ('2005-07-20 00:00:00'::timestamp without time zone) at
> 	time zone 'Europe/Paris';
> 	        timezone
> 	------------------------
> 	 2005-07-19 18:00:00-04
> 	(1 row)
> 	
> and tested that for UTC also.
> 
> It was hard to figure out how to cleanly adjust the time zone.  I added
> some comments explaining the process.
> 
> ---------------------------------------------------------------------------
> 
> Andrew - Supernews wrote:
> > On 2005-07-22, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> > >> 
> > >> select (CURRENT_DATE + '05:00'::time)::timestamp
> > >>    at time zone 'Canada/Pacific';
> > >>         timezone        
> > >> ------------------------
> > >>  2005-07-19 22:00:00+00
> > >> (1 row)
> > >> 
> > > 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.
> > 
> > Which is of course completely wrong.
> > 
> > Let's look at what should happen:
> > 
> >  (date + time) = timestamp without time zone
> > 
> > '2005-07-20' + '05:00' = '2005-07-20 05:00:00'::timestamp
> > 
> >  (timestamp without time zone) AT TIME ZONE 'zone'
> > 
> > When AT TIME ZONE is applied to a timestamp without time zone, it is
> > supposed to keep the _same_ calendar time and return a result of type
> > timestamp with time zone designating the absolute time. So in this case,
> > we expect the following to happen:
> > 
> >  '2005-07-20 05:00:00'              (original timestamp)
> >  -> '2005-07-20 05:00:00-0700'      (same calendar time in new zone)
> >  -> '2005-07-20 12:00:00+0000'      (convert to client timezone (UTC))
> > 
> > So the conversion is being done backwards, resulting in the wrong result.
> > 
> > -- 
> > Andrew, Supernews

-- 
  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

pgsql-patches by date

Next:From: Bruce MomjianDate: 2005-07-23 14:53:43
Subject: Re: [HACKERS] regressin failure on latest CVS
Previous:From: Luke LonerganDate: 2005-07-23 04:09:10
Subject: Re: [PATCHES] COPY FROM performance improvements

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