Re: Timezones (in 8.5?)

> hernan> The support of timezones is really crippled
>  hernan> now.
>
> Crippled how?

Well, among other things, no builtin date-timetype allows me to save
the timezone (or even the offset).
No type allows to treat this three datetimes as different values.
'2010-07-27 10:30 GMT+4' '2010-07-27 09:30 GMT+5' '2010-07-27 10:30 GMT+0'
The ANSI spec at least permits that.

> The example you gave is easily handled in pg as follows:

Well, using compound types one can handle practically everything...
My point is expressiveness. Basic datatypes should ideally correspond to
the most typical data that one which to store/retrive/manipulate in a DB.
And my claim is that most date-time values found in real life can be neatly
classified in the types I mentioned (basically: physical instants of time, or
civil date-times), and that they should not be confused.
Hence, for example an operation as [TIMESTAMP] + "1 MONTH" should
not be allowed (incompatible types).
Hence, when I ask PG "store the datetime "2010-Jul-27, 10:30:00 (at TZ
Chile/Santiago)",
it should not do (as today) "ok, let me check the zic table for that
TZ... aha, offset +4,
so you meant the UTC time 2010-Jul-27 14:30:00 ...saved" But I didn't
mean that, I meant
what I said (a civil date). The "bridging" (conversion to physical
time) should only be made
when (if) needed.
I also claim, BTW, that the DB should never rely on its local TZ. If
some SQL query (eg: select all
orders confirmed in January) can return different sets by changing the
TZ of the DB server,
something is wrong.

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

I'd said that calendar events are the most typical case of civil
date-times (most other
date-times, i think, are in fact timestamps, i.e. physical times: eg
when a record was created,
a blog post, etc). When I record an appointment with my dentist at
"9:30 (at my TZ)" I'm not thinking
of a point of time, but a civil date-time. PG does not me allow to
save (cleanly and robustly) such
a basic data item. You must resort to a compound type, and plug the
semantic yourself.
I think that, if the date-time types were more consistent and natural,
there would be
no need to make assumptions about timezones specifications can change or not,
the issue would not arise. (BTW, in my country the timezones indeed change, and
most unpredictably ,sadly; but that's not my motivation)

Hernán J. González
Buenos Aires, Argentina
http://hjg.com.ar/