Re: Timezones (in 8.5?)

>>>>> "hernan" == hernan gonzalez <hgonzalez(at)gmail(dot)com> writes:

>> Perhaps the OP should explain exactly what real-world problems
>> he's trying to solve.  As noted in the discussion you linked,
>> there's not a lot of enthusiasm around here for getting closer to
>> the spec's datetime handling simply because it'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 SQL
hernan> spec, I agree in this regard it is unsatisfactory (to put it
hernan> midly). But I'm also disatisfied with the current Postgresql
hernan> implementation, the types TIMESTAMP and TIMESTAMP WITH
hernan> TIMEZONE are in the middle of being SQL compliant and being
hernan> really useful. The support of timezones is really crippled
hernan> 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 at
hernan> datetime 2010-Jul-27, 10:30:00, with TZ "Chile/Santiago",
hernan> (GMT+4 hence it corresponds to UTC time 2010-Jul-27
hernan> 14:30:00). But some days afterwards, his government decides
hernan> to change the country TZ to GMT+5.

hernan> Now, when the day comes... should that reminder trigger at
hernan> A) 2010-Jul-27 10:30:00 "Chile/Santiago" = UTC time 2009-Jul-27 15:30:00
hernan> or
hernan> 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 John
hernan> 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 tell 10:30")? In that case, A)
hernan> is the correct answer. Or did he mean a "physical instant of
hernan> time", a point in 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)