Thread: Timezones (in 8.5?)
Are there any plans to (is anybody working on) implement better timezone support in postgresql for 8.5 ? Specifically, store the timezone info -instead of just the timestamp as UTC ? http://wiki.postgresql.org/wiki/Todo#Dates_and_Times Hernán J. González
On Tue, Nov 17, 2009 at 10:21 AM, hernan gonzalez <hgonzalez@gmail.com> wrote: > Are there any plans to (is anybody working on) implement better > timezone support in postgresql > for 8.5 ? Specifically, store the timezone info -instead of just the > timestamp as UTC ? > http://wiki.postgresql.org/wiki/Todo#Dates_and_Times You might want to use the word "different" rather than the word "better", because the current behavior is quite useful and I think many people would be unhappy if it were to go away. I think there's also some debate about whether we want this at all. See here: http://archives.postgresql.org/pgsql-hackers/2009-09/msg00964.php One random thought - I am not aware that we currently have a "time zone" type in which to store a time zone in. Is there any value in having such a thing vs. just using varchar? ...Robert
Robert Haas <robertmhaas@gmail.com> writes:
> One random thought - I am not aware that we currently have a "time
> zone" type in which to store a time zone in. Is there any value in
> having such a thing vs. just using varchar?
The main potential advantage seems to be faster lookup of the zone's
associated data ... but I think we already keep the data in a hashtable
indexed by hash of the zone name, so the gain might be pretty marginal.
A specialized type *might* provide some notational advantage for writing
operators, eg maybe "timestamp @ zone" would be sensible. But this is
speculative without some clearer idea of what operations you'd want.
And anyway it's not clear that text wouldn't work just as well there.
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.
regards, tom lane
> 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.
I'm not much interested in the compliance with the ANSI SQL spec, I
agree in this regard it is unsatisfactory (to put it midly).
But I'm also disatisfied with the current Postgresql implementation,
the types TIMESTAMP and TIMESTAMP WITH TIMEZONE are in the middle of
being SQL compliant and being really useful. The support of timezones
is really crippled now.
I understand, though, that backward compatibily is critical, and I'm
surely unaware of many implementation issues.
Anyway (long rambling follows - and excuse my english)...
We know that, even ignoring ANSI spec and postgresql compatibility for
one moment, even before considering date-time arithmetic and DTS
issues, date-time handling is notoriously difficult to formalize
satisfactorily. And, come to look at it, it's not a Postgresql
problem, nor a SQL problem: I believe there is NO standard for
store/serialize/represent a "date-time value", with all the
complexities that the concept has in human usage (ISO 8601, as
ANSI-SQL, just considers GMT offsets, not real timezones).
Let me present a simple real world scenario -to look at not from the
implementation point of view, but from the user:
- John records in his calendar a reminder for some event at datetime
2010-Jul-27, 10:30:00, with TZ "Chile/Santiago", (GMT+4 hence it
corresponds to UTC time 2010-Jul-27 14:30:00). But some days
afterwards, his government decides to change the country TZ to GMT+5.
Now, when the day comes... should that reminder trigger at A) 2010-Jul-27 10:30:00 "Chile/Santiago" = UTC time
2009-Jul-2715:30:00
or B) 2010-Jul-27 9:30:00 "Chile/Santiago" = UTC time 2009-Jul-27 14:30:00 ?
There is no correct answer, unless one knows what John actually meant
when he said "please ring me at "2010-Jul-27, 10:30:00
TZ=Chile/Santiago"
Did he mean a "civil date-time" ("when the clocks in my city tell
10:30")? In that case, A) is the correct answer.
Or did he mean a "physical instant of time", a point in the continuus
line of time of our universe, say, "when the next solar eclipse
happens". In that case, answer B) is the correct one.
I believe that this distinction between two realms: one related to
(say) "physical time" and the other to (say) "civil date-time", is the
key to put some order... conceptually, at least (I'm not speaking
about feasibility for now). This is the approach of some Date-Time
APIs, for example the "Joda" Java library
http://joda-time.sourceforge.net/ (headed to replace soon
https://jsr-310.dev.java.net/ the original ugly JDK Date-Calendar
API) and I believe it's the right way.
In this approach, we would have two entirely different types (or
family of types) -no castings allowed.
An "instant" is a "physical time", a point in the time continuum.
A "partial date time spec" (or "partial civil datetime") is just a
tuple of values {year,month,day, hour,min,sec,usec,TZ_id} some of
which might be empty/unspecified.
Conversion from "instant" to "civil datetime" is only allowed if a TZ
is also specified (well, also a "Calendar" spec, if non-gregorian
dates are to be dealt with).
Conversion from "partial civil datetime" to "instant" is only allowed
if all fields are non-empty (again, assuming a "Calendar").
Similar distintion goes for "intervals" or "durations".
Postgresql implementation (and ANSI-SQL), for all date-time data,
revolves around the "physical time" concept: that is what it is
ultimately stored, that's what it's tought as the "real thing" (the
rest are input/output and arithmetic issues).
(Rather disgressing: even the DATE type is treated as a point in time,
as a DateTime with time=00:00:00 ; I think this is bad, conceptually,
when I think of "2010-Jul-27" I think of a date, not of the instant of
time "2010-Jul-27 00:00:00", they are different concepts; this is NOT
analogous to INT 10 => FLOAT 10.0 )
Because of this (IMHO) conceptual limitation, the availabily of the
two types "TIMESTAMP" "TIMESTAMP WITH TIME ZONE" results,
unfortunately, much less useful than it could have been.
If I were to reimplement the date-time data types, without much
regarding ANSI-SQL standard and Postgresql compatibility (a little too
much to ask, I know) I'd propose:
TIMESTAMP: ("instante") just a point in time, purely physical (as it
name suggest!). UTC encoded.
(input format could accept unix time or standard datetime format, with
default/server TZ; output format could output explicit GMT offset, to
support dump/restore robustly)
DATETIME: (call it "TIMESTAMP WITH TIME ZONE" if you wish but... is a
very different thing)
a full "civil" date time specification {year,month,day,
hour,min,sec,usec,TZ} (Of course, internally it could be stored as UTC
+ TZ_id )
Can be converted to TIMESTAMP, (but no casting allowed!), but the
result might vary if the TZ tables are changed (see my example above).
LOCAL_DATETIME: a full "civil" date time specification
{year,month,day, hour,min,sec,usec} with NO TZ.
Cannot be converted to DATETIME (or TIMESTAMP), except if a TZ_id is
also specified.
(This type might be merged with DATETIME if we allow empty TZ_id
values; but NEVER assume the server TZ as default TZ when empty!)
DATE: just a civil "date" {year,month,date} . Can be converted to
LOCAL_DATIME only by adding the remaining fields.
And similar for intervals...
Of course, there are many implementation details (some "time zone"
type or codification - efficient caching of DATETIME
operations/conversions - lots of input/output formatting issues,
interfaces, dump/restore) - and above all, compatibily with pg and
ANSI. Anyway, I dream of seing Postgresql going this way :-)
Best regards
Hernán J. González
Buenos Aires, Argentina
http://hjg.com.ar/
hernan gonzalez <hgonzalez@gmail.com> wrote: > I believe that this distinction between two realms: one related to > (say) "physical time" and the other to (say) "civil date-time", is > the key to put some order... conceptually, at least (I'm not > speaking about feasibility for now). This is the approach of some > Date-Time APIs, for example the "Joda" Java library > http://joda-time.sourceforge.net/ (headed to replace soon > https://jsr-310.dev.java.net/ the original ugly JDK Date-Calendar > API) and I believe it's the right way. Congratulations on the most sane and thoughtful discussion of this I've seen! In our shop we had so many problems with the "physical time" based implementation of dates, times, and timestamps in Java that we wrote our own library to cover our needs. I hadn't heard about Joda; we should probably look at it to see if we can migrate from our home-grown solution. One thing you didn't address is the "end-of-month" issues -- how do you handle an order that someone pay a set amount on a given date and monthly thereafter, when the date might be past the 28th? I'm curious to hear your opinion on that topic. I have seen in this real-world financial applications several times. They have usually wanted to go to the last day of the month when there aren't enough days in a given month, but then go back out to the original day-of-month whenever possible; but sometimes the payment "one month" after the 31st of January has to be 30 days past the 1st of the next month. The SQL standard solution to this is much ridiculed here, even though I suspect many have seen monthly bills or statements at some point in their lives.... ;-) -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> hernan gonzalez <hgonzalez@gmail.com> wrote:
>> I believe that this distinction between two realms: one related to
>> (say) "physical time" and the other to (say) "civil date-time", is
>> the key to put some order... conceptually, at least (I'm not
>> speaking about feasibility for now).
> Congratulations on the most sane and thoughtful discussion of this
> I've seen!
Yeah. As Hernan says, our notion of timestamptz corresponds to physical
time, although the input/output conventions for it blur that rather
badly. You can use the AT TIME ZONE constructs to convert between
physical and civil times, but only according to the system's current
understanding of the civil calendar, which will change anytime you
install an update of the zic database. We haven't got a datatype that
corresponds directly to "an instant in civil time" --- you could store
timestamp-without-tz and a time zone name, but it's not built in.
I could see developing new types that correspond more directly to
physical and civil time --- the first is probably exactly the same as
timestamptz except it always displays in UTC, and the second needs two
fields. I think that trying to substitute either of these for the
existing types is probably a lost cause though.
Trying to deal with different civil calendars (changes in zic database
rules) seems way too hard for what it would buy us. I think if you're
using the civil time type, you're assuming that "10AM Nov 17 2009" means
"10AM local time", even if the powers that be change the GMT offset
sometime during the period that the data value is of interest.
> One thing you didn't address is the "end-of-month" issues -- how do
> you handle an order that someone pay a set amount on a given date and
> monthly thereafter, when the date might be past the 28th?
This seems to be an arithmetic operator issue and not directly a
property of the type --- you could imagine different "datetime + interval"
operators giving different answers for this but still working on the
same underlying civil-time type.
regards, tom lane
>>>>> "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)
Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: > If he meant (A), then you store the event as: > (ts,tz) = (timestamp '2010-07-27 10:30:00', > 'Chile/Santiago') > 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') You seem to be agreeing that these problems can't be solved without storing a time zone string in addition to the timestamp. As I read it, Hernán was wishing for types which include this, rather than having to do the above dance with multiple values. -Kevin
>>>>> "Kevin" == "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> If he meant (A), then you store the event as:>> (ts,tz) = (timestamp '2010-07-27 10:30:00',>> 'Chile/Santiago')>> If hemeant (B), then you store the event as>> (tsz,tz) = (timestamp '2010-07-27 10:30:00' at time zone>> 'Chile/Santiago', 'Chile/Santiago')Kevin>You seem to be agreeing that these problems can't be solvedKevin> without storing a time zone stringin addition to theKevin> timestamp. As I read it, Hernán was wishing for types whichKevin> include this, rather thanhaving to do the above dance withKevin> multiple values. Right, but including more data in a single type is the wrong approach, since it complicates the semantics and interferes with normalization. For example, if you have a type T which incorporates a timestamp and a timezone, what semantics does the T = T operator have? What semantics apply if the definitions of timezones change? What if you're storing times of events at specific places; in that case you want to associate the timezone with the _place_ not the event (so that if the timezone rules change, moving the place from one timezone to another, you only have to change the place, not all the events that refer to it). -- Andrew (irc:RhodiumToad)
On Wed, Nov 18, 2009 at 11:18 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: >>>>>> "Kevin" == "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > > >> If he meant (A), then you store the event as: > >> (ts,tz) = (timestamp '2010-07-27 10:30:00', > >> 'Chile/Santiago') > > >> 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') > > Kevin> You seem to be agreeing that these problems can't be solved > Kevin> without storing a time zone string in addition to the > Kevin> timestamp. As I read it, Hernán was wishing for types which > Kevin> include this, rather than having to do the above dance with > Kevin> multiple values. > > Right, but including more data in a single type is the wrong approach, > since it complicates the semantics and interferes with normalization. > For example, if you have a type T which incorporates a timestamp and a > timezone, what semantics does the T = T operator have? What semantics > apply if the definitions of timezones change? What if you're storing > times of events at specific places; in that case you want to associate > the timezone with the _place_ not the event (so that if the timezone > rules change, moving the place from one timezone to another, you only > have to change the place, not all the events that refer to it). Also, if someone DOES want to use these together, isn't that what composite types are for? ...Robert
Robert Haas <robertmhaas@gmail.com> wrote: > On Wed, Nov 18, 2009 at 11:18 PM, Andrew Gierth > <andrew@tao11.riddles.org.uk> wrote: >>>>>>> "Kevin" == "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> >> >> If he meant (A), then you store the event as: >> >> (ts,tz) = (timestamp '2010-07-27 10:30:00', >> >> 'Chile/Santiago') >> >> >> 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') >> >> Kevin> You seem to be agreeing that these problems can't be solved >> Kevin> without storing a time zone string in addition to the >> Kevin> timestamp. As I read it, Hernán was wishing for types >> Kevin> which include this, rather than having to do the above >> Kevin> dance with multiple values. >> >> Right, but including more data in a single type is the wrong >> approach, since it complicates the semantics and interferes with >> normalization. Or, one could say, it encapsulates the semantics within the type's operators, avoiding the need to repeat the logic everywhere, or to use more verbose explicit function calls. >> For example, if you have a type T which incorporates a timestamp >> and a timezone, what semantics does the T = T operator have? What >> semantics apply if the definitions of timezones change? I'd rather sort that out once and implement the desired semantics in the operators for a new type than to count on application programmers doing it consistently each time. Wouldn't you? >> What if you're storing times of events at specific places; in that >> case you want to associate the timezone with the _place_ not the >> event (so that if the timezone rules change, moving the place from >> one timezone to another, you only have to change the place, not all >> the events that refer to it). I'm not sure I quite followed you there, but Hernán's example specifically called for storing 'Chile/Santiago', not a UTC offset or something as easily changed as the 'CLT' or 'CLST' time zone designations -- so it is tied to a place rather more closely than anything else. I think that was part of his point -- that for civil time you care about what the clock on a typical business's wall at that place will read on that date, regardless of what changes might happen in time zone definitions. > Also, if someone DOES want to use these together, isn't that what > composite types are for? I'm going to plead both ignorance and laziness here. My use of composite types is limited, so I don't know, offhand, whether you can define a set of operators for a composite type which will provide the consistent behavior with convenient operators which Hernán seems to want. If they allow that, then it certainly seems like the way to go, so that the component parts of the abstraction we've been calling civil time can be easily accessed. If not, they're not suited to what Hernán wants (as I understand it). For the record, this discussion has made me realize that I don't care as much about including such information with tsz as with ts. The tsz enhancement wouldn't change the semantics of the object at all, as far as I can see, beyond it's default presentation when you turn it into a string. That's worth something, but pales in comparison to the value of the civil time concept, which would actually match the common usage in scheduling business meetings and most other every-day activities. I think the popularity of physical time is that it is so concrete. The reality of usage of date and time, though, is that various abstractions which aren't tightly coupled to physical time are common and useful. The civil time issues are one aspect of that. (And as far as I'm concerned, leap seconds can be totally ignored for civil time -- there's a nice round clock up on my wall with a big hand and a little hand and a second hand all spinning around, and there's no place on that clock face for a 61st or 62nd second in any minute, ever.) And those who don't think it's useful be able to add one month to the 31st of January and get a date as a result to which you can add one month and get the 31st of March -- well, come the cultural revolution I plan to see to it that they do nothing but write financial applications for five years.... :-) -Kevin
>>>>> "Kevin" == Kevin Grittner <Kevin.Grittner@wicourts.gov> writes: >>> For example, if you have a type T which incorporates a timestamp>>> and a timezone, what semantics does the T = T operatorhave? What>>> semantics apply if the definitions of timezones change?Kevin> I'd rather sort that out once and implementthe desiredKevin> semantics in the operators for a new type than to count onKevin> application programmers doingit consistently each time.Kevin> Wouldn't you? No, because the desired semantics are not the same for everyone, so even if you take just the two examples I gave above, you're already into combinatorial explosion with four different types needed. By keeping it as a composite value, you allow the app to define the semantics it needs.>>> What if you're storing times of events at specific places; in>>> that case you want to associate thetimezone with the _place_ not>>> the event (so that if the timezone rules change, moving the place>>> from one timezoneto another, you only have to change the place,>>> not all the events that refer to it).Kevin> I'm not sure I quitefollowed you there, but Hernán's exampleKevin> specifically called for storing 'Chile/Santiago', not a UTCKevin> offsetor something as easily changed as the 'CLT' or 'CLST'Kevin> time zone designations -- so it is tied to a place rathermoreKevin> closely than anything else. But those place definitions do occasionally change. For example, some US states can change timezone at county level; suppose a state that was previously all one timezone decides to change timezone or DST observance for all except a few counties that remain on the previous setting. So places within those counties will have to change timezone name from America/Somestate to America/Somestate/Oddcounty while places in the rest of the state stay with America/Somestate. The fact that geographic names are used for timezones doesn't mean that the timezone name applicable to a given place doesn't change; timezones in the database can split when rule changes happen that don't affect the full extent of the previous zone; this leads to two or more zones which have identical definitions up to some date, and different definitions after it. (Zones can only split, they can't merge, due to the necessity of keeping historical changes.) Kevin> I think that was part of his point -- that for civil time youKevin> care about what the clock on a typical business'swall at thatKevin> place will read on that date, regardless of what changes mightKevin> happen in time zone definitions. Right, but if timezone _boundaries_ change, this can't happen without some manual corrections. (If the timezone _rules_ change without changing the boundaries, then just updating the tzdata is enough if you designed the db correctly.) -- Andrew.
> 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/
On Nov 19, 2009 1:18am, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:<br />> <br />> Right, but
includingmore data in a single type is the wrong approach,<br />> since it complicates the semantics and interferes
withnormalization.<br />> For example, if you have a type T which incorporates a timestamp and a<br />> timezone,
whatsemantics does the T = T operator have? What semantics<br />> apply if the definitions of timezones change? <br
/><br/>I dont get the thing about normalization, there's complete ortoghonality in my approach.<br />And when you say
"complicatesthe semantic" I'd say "enrich the semantics" (and even<br />clarify it) so that it fits more neatly to the
typicalusage of dates and times in real life.<br />For "datetimes with tz" the equality (and comparison) operator is
nottrivial, roughly <br />in the same sense that date-time arithmetic is not trivial when one stops thinking of<br
/>datetimesas "physical time". So is life. Should the datetimes <br />'2010-07-27 9:30 Chile' and '2010-07-27 10:30
Argentine'(GMT+4 and GMT+3 respec)<br />be considered equal? It's arguable; but the ambiguity (just a matter of
adoption)reflects<br />reality. We can discuss it and adopt some consistent criteria.<br /><br />> What if you're
storing<br/>> times of events at specific places; in that case you want to associate<br />> the timezone with the
_place_not the event (so that if the timezone<br />> rules change, moving the place from one timezone to another,
youonly<br />> have to change the place, not all the events that refer to it).<br /><br />I'm not sure I undestand
youhere. I'm claiming that timezone rules alterations<br />(zic files changes) should always be supported by the db
implementation,without<br />needing of touching your data. And I believe that timestamps (i.e. physical times)<br />are
inpractice almost never associated to timezone information. If you want to store<br />"the instant of last solar
eclipse"you normally store the timestamp, a timezone<br />might only be useful for displaying (or as an adittional
info,not really associated to the event)<br />A border case would be "store the instant of the death of John Lennon".
Youmight<br />store the TZ here if you are interested in the civil time (so you can answer, for example,<br />¿how many
rockstars died in morning/afternoon?). But then, again, you are here actually<br />storing a civil date (local
date-timeplus TZ). The only problematic case i can envision<br />is to intend to store a physical time in the future
withTZ, but frankly it is difficult to<br />think of this scenario (and even more difficult to think of needing to
operatewith that<br />data as a whole; hence, in this case, to store the two fields separatadely makes sense).<br /><br
/>I'mbeing dense, and this might be a lost cause, but anyway, perhaps some day in the future<br />this might be of some
use:<br/><br />I strongly believe that, if one could sample the real needings and usage of date-time types in <br
/>applicationsin this world, and taking apart types DATE (very frequent, but rather straightforward), <br />and TIME
(notso relevant) and intervals (other issues here, much related to datetimes), the <br />overwhelming majority would
fall( conceptually) into these three types:<br /><br />- TIMESTAMP (physical time - no TZ - no civil time implied)<br
/>-LOCAL DATETIME (civil time, no TZ)<br />- DATETIME (civil time with TZ => togheter with zic tables, implies a
physicaltime)<br /><br />And of these three -I'd bet- the first is (conceptually) the most common, by a wide margin.
<br/><br />As the name TIMESTAMP implies, it frequently records the moment of a event (in the DB corresponds<br />
frequentlyto the creation or alteration of a record, frequently via a "now()" default or such). <br />Examples: the
timestampof messages in a mailing list, or issues in a bugtracker, or posts/articles in a blog/Cms.<br />Sometimes it
ismodifiable by the user. Sometimes it is displayed (as a civil date, of course) according <br />to some TZ implied
somewhereelse. It's normal that users with differnt TZ sees this event each <br />with its own TZ; and one is not
directlyinterested on obtaining (say) an "inherent" civil datetime for the<br />event (for example one is not
interestedin asking what posts where generated at midnight<br />acording to the localtime of the user that created
it).<br/><br />The LOCAL DATETIME is only of use for civil date-times, when one is not directly interested in<br
/>asociateevents with real (physicial time) - this cannot be compared with a real time (it cant trigger alarms, eg)<br
/>Or,more rarely, when the TZ is implied somehere else (in the application, not it the DB server!).<br /><br />The
DATETIMEis equivalent to the compound type {LOCAL_DATETIME,TZ}. Here the "civil date-time" is again<br />the primary
conceptone deals with, but in a given place in the world (TZ), so it implies also (with the assistance of a zic
table)<br/>a real time. This type is, IMHO, less frequent than the others. The typical use is for calendars or
schedulers.<br/><br />One could, a propos Andrew's observation, consider a fourth type: TIMESTAMP WITH TZ. But it seems
overkill:<br />except for ZIC changes, the correspondence with DATETIME is univocal (BTW, this is why in the Jodatime
API<br/>-which does not deal with persistence- this concepts are strictly equivalent). Given this nearly-equivalence,
andthat<br /> the needing of this type in real life is (IMO) almost null, I think that DATETIME is the one to
survive.<br/><br />(One could even propose a fifht type: a TIMESTAMP WITH GMT OFFSET (roughly the ANSI proposal),
which<br/>would be equivalent to have a timestamp AND a local datetime; this is more easy to deal with than
timezones,<br/>but (as was discussed here before) is too limited (does not allow artithmetic) and is not orthogonal
withthe real useful types.)<br /><br />Regarding implementation:<br /><br /> TIMESTAMP is straightforward, more or less
thesame as today: stored as UTC, can be <br />input/output in ISO 8601 format (the client/server can use the offset
theylike, internally it's translated to GMT+0)<br /><br />LOCAL_DATETIME also is straightforward, also stored as UTC
(asin GMT+0) . BUT <br />- input/output in ISO 8601 format should not allow/produce GMT offset<br />- the similarity of
implementationshould not leak upwards. This types are incompatible, cannot be compared, etc<br /><br />DATETIME is the
difficultone, of course.<br /> - Equivalent to the pair {LOCAL_DATETIME,TX_id} (occupies more space)<br /> - Requires
somecatalog table or something akin to codify consistently the timezones as numbers (included in pg_dump output?)<br />
-Requires new definitions for input/output (and deal with some ambiguities, particulary in DST transitions)<br /> -
Requiressome semantic definitions (orderig, equality) <br /> - Some arithmetics (which involve convertion to physic
time)may be expensive, might require some aggresive caching of time (zic) calculations.<br /><br />Global
considerations:<br /> - Backward compatibility?<br /> - SQL spec compatibility? (is worsened?) (deprecate TIMESTAMP
WITHTIMEZONE?)<br /> - implement conversion functions - castings (how strict?)<br /> - discuss/implement interval
types/functions<br/> - interfaces (JDBC...)<br /><br />A bit of work, granted... (I might help)... but I bet that the
mostdifficult work, by far, is to <br />reach an agreement :-)<br /><br />Thanks for reading.<br /><br />Hernán J.
González
hernan gonzalez wrote: > > 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. I think there is general agreement that we should have a timezone data type which validates against pg_timezone_names().name. It might be enough to just document how users can create such a domain data type, but I don't know of a way to do that. Is this a TODO? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
>>>>> "Bruce" == Bruce Momjian <bruce@momjian.us> writes: Bruce> I think there is general agreement that we should have aBruce> timezone data type which validates againstBruce> pg_timezone_names().name. What happens when pg_timezone_names output changes? (which it can do, especially if the install is using the OS tzdata; even if not using OS tzdata, it's not expected to be stable even between point releases) -- Andrew.
Andrew Gierth wrote: > >>>>> "Bruce" == Bruce Momjian <bruce@momjian.us> writes: > > Bruce> I think there is general agreement that we should have a > Bruce> timezone data type which validates against > Bruce> pg_timezone_names().name. > > What happens when pg_timezone_names output changes? (which it can do, > especially if the install is using the OS tzdata; even if not using OS > tzdata, it's not expected to be stable even between point releases) Uh, wow, yea, that would invalidate stored data --- yuck. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Nov 28, 2009, at 5:40 PM, Bruce Momjian wrote: > I think there is general agreement that we should have a timezone data > type which validates against pg_timezone_names().name. It might be > enough to just document how users can create such a domain data type, > but I don't know of a way to do that. Is this a TODO? From http://justatheory.com/computers/databases/postgresql/citext-patch-submitted.html CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$ BEGIN PERFORM now() AT TIME ZONE tz; RETURN TRUE; EXCEPTION WHEN invalid_parameter_value THEN RETURN FALSE; END; $$ language plpgsql STABLE; CREATE DOMAIN timezone AS CITEXT CHECK ( is_timezone( value ) ); It could also be TEXT I suppose, but "America/Los_Angeles" and "america/los_angeles" should be considered the same. Best, David
2009/11/29 David E. Wheeler <david@kineticode.com>: > On Nov 28, 2009, at 5:40 PM, Bruce Momjian wrote: > >> I think there is general agreement that we should have a timezone data >> type which validates against pg_timezone_names().name. It might be >> enough to just document how users can create such a domain data type, >> but I don't know of a way to do that. Is this a TODO? > > From http://justatheory.com/computers/databases/postgresql/citext-patch-submitted.html > > CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$ > BEGIN > PERFORM now() AT TIME ZONE tz; > RETURN TRUE; > EXCEPTION WHEN invalid_parameter_value THEN > RETURN FALSE; > END; > $$ language plpgsql STABLE; > > CREATE DOMAIN timezone AS CITEXT > CHECK ( is_timezone( value ) ); > > It could also be TEXT I suppose, but "America/Los_Angeles" and "america/los_angeles" should be considered the same. nice :) Pavel > > Best, > > David > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >