Re: Timezones (in 8.5?) - Mailing list pgsql-hackers

>>>>> "hernan" == hernan gonzalez <hgonzalez@gmail.com> writes:
>> Perhaps the OP should explain exactly what real-world problems>> he's trying to solve.  As noted in the discussion
youlinked,>> there's not a lot of enthusiasm around here for getting closer to>> the spec's datetime handling simply
becauseit's the spec; that>> part of the spec is just too broken for that to be a credible>> argument. 
hernan> I'm not much interested in the compliance with the ANSI SQLhernan> spec, I agree in this regard it is
unsatisfactory(to put ithernan> midly).  But I'm also disatisfied with the current Postgresqlhernan> implementation,
thetypes TIMESTAMP and TIMESTAMP WITHhernan> TIMEZONE are in the middle of being SQL compliant and beinghernan> really
useful.The support of timezones is really crippledhernan> now. 

Crippled how?

The example you gave is easily handled in pg as follows:
hernan>  - John records in his calendar a reminder for some event athernan> datetime 2010-Jul-27, 10:30:00, with TZ
"Chile/Santiago",hernan>(GMT+4 hence it corresponds to UTC time 2010-Jul-27hernan> 14:30:00). But some days afterwards,
hisgovernment decideshernan> to change the country TZ to GMT+5. 
hernan> Now, when the day comes... should that reminder trigger athernan>   A) 2010-Jul-27 10:30:00  "Chile/Santiago"
=UTC time  2009-Jul-27 15:30:00hernan> orhernan>   B) 2010-Jul-27  9:30:00  "Chile/Santiago"  = UTC time  2009-Jul-27
14:30:00? 
hernan> There is no correct answer, unless one knows what Johnhernan> actually meant when he said "please ring me at
"2010-Jul-27,hernan>10:30:00 TZ=Chile/Santiago" Did he mean a "civil date-time"hernan> ("when the clocks in my city
tell10:30")? In that case, A)hernan> is the correct answer.  Or did he mean a "physical instant ofhernan> time", a
pointin the continuus line of time of our universe,hernan> say, "when the next solar eclipse happens". In that
case,hernan>answer B) is the correct one. 

If he meant (A), then you store the event as:
(ts,tz) = (timestamp '2010-07-27 10:30:00',          'Chile/Santiago')
and decide when it happens using (ts at time zone tz), evaluated on
the fly. This way, when you install an update in your zic database to
cope with the change of tz, the computed value of the physical time
changes, but it still shows the same calendar time.

If he meant (B), then you store the event as
(tsz,tz) = (timestamp '2010-07-27 10:30:00' at time zone 'Chile/Santiago',           'Chile/Santiago')
(note that tsz is now of type timestamp with time zone). This fixes the
physical time, and when you install the zic update, the displayed calendar
time changes, in order to keep the physical time the same.

If you're writing a calendaring app that wants to allow storing both kinds
of events (I've yet to see such an app that actually makes this distinction,
most seem to work on the assumption that timezones don't change), all the
tools for it are currently available in postgres.

--
Andrew (irc:RhodiumToad)