Re: Timezones (in 8.5?)
От | hernan gonzalez |
---|---|
Тема | Re: Timezones (in 8.5?) |
Дата | |
Msg-id | 48692c2d0911180550m1727c0ebkdae4bf6bea443a22@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Timezones (in 8.5?) (Andrew Gierth <andrew@tao11.riddles.org.uk>) |
Ответы |
Re: Timezones (in 8.5?)
(Bruce Momjian <bruce@momjian.us>)
|
Список | pgsql-hackers |
> 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/
В списке pgsql-hackers по дате отправления: