Hi Adrian, thanks for your answer. I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant?
If i just want to store a reference to "Dec 19th" without adding an innecesary reference to a "dummy" time, like 00:00:00 (for time zone tracking's sake), wouldn't it be elegant to be able to say "Dec 19th (GMT-3)" ? On the other hand, I don't really see the reasons of this statement: "Although the date type *cannot *have an associated time zone, the time type can." Why is this so? I'm no guru, but I don't see any obvious technical impossibility to do so. Is this so just because SQL standard says so? Can it be possible that SQL standard is a little short on this kind of need? Again, of course I can always use a timestamp set to 00:00:00 just to use its time zone tracking capabilities, but It is just as dirty as any other patch. A date is a date, and a timestamp is a timestamp, and both, used independently, should be able to keep track of its associated time zone, I think. Am I wrong on this? Apart from what SQL Standard may say, for instance. On Sat, Nov 28, 2009 at 4:00 PM, Adrian Klaver <akla...@comcast.net> wrote: > On Saturday 28 November 2009 3:43:02 am Eduardo Piombino wrote: > > Hello list, this is my first msg here. I hope this is the correct place > for > > this subject, I couldn't find any more specific list for this. > > > > This thought had been bugging me for some time now and I thought it was > > time to share it with you pg gurus. > > > > Why in god's sake is there not a "date with time zone" data type? > > I mean, in the same manner that every country does not have the same time > > (due to the time zone they are in), they also don't have to be in the > same > > day (for the same reason). Maybe it's January 10th in one place, and > > January 11st a couple of time zones ahead. > > > > So, in the same way that a simple "time" data type is not enough for > > precise time specification on multi time zone setups, a simple "date" > data > > type is also not enough for a precise date specification in those setups. > > > > Of course you can always set another column, specifying that that "date" > > actually corresponds to a specific timezone, but in the same manner that > u > > dont need an extra column for time values (cause u have the "time with > time > > zone"), you shouldn't be needing to create another one to host the time > > zone for the date. > > > > I don't know, am I crazy? > > Thanks a lot. > > > > Eduardo. > > The best explanation I can offer comes from the manual. > > http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html > > " PostgreSQL endeavors to be compatible with the SQL standard definitions > for > typical usage. However, the SQL standard has an odd mix of date and time > types > and capabilities. Two obvious problems are: > > * > > Although the date type cannot have an associated time zone, the time > type > can. Time zones in the real world have little meaning unless associated > with a > date as well as a time, since the offset can vary through the year with > daylight-saving time boundaries. > * > > The default time zone is specified as a constant numeric offset from > UTC. > It is therefore impossible to adapt to daylight-saving time when doing > date/time arithmetic across DST boundaries. > > To address these difficulties, we recommend using date/time types that > contain > both date and time when using time zones. We do not recommend using the > type > time with time zone (though it is supported by PostgreSQL for legacy > applications and for compliance with the SQL standard). PostgreSQL assumes > your > local time zone for any type containing only date or time. " > > -- > Adrian Klaver > akla...@comcast.net >
