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
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
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
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
On Sat, Nov 28, 2009 at 4:00 PM, Adrian Klaver <aklaver(at)comcast(dot)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
> > 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
> > 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"
> > 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
> > dont need an extra column for time values (cause u have the "time with
> > 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.
> " PostgreSQL endeavors to be compatible with the SQL standard definitions
> typical usage. However, the SQL standard has an odd mix of date and time
> and capabilities. Two obvious problems are:
> Although the date type cannot have an associated time zone, the time
> 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
> 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
> both date and time when using time zones. We do not recommend using the
> time with time zone (though it is supported by PostgreSQL for legacy
> applications and for compliance with the SQL standard). PostgreSQL assumes
> local time zone for any type containing only date or time. "
> Adrian Klaver
In response to
pgsql-general by date
|Next:||From: Scott Marlowe||Date: 2009-11-28 23:49:47|
|Subject: Re: vacuumdb -z do a reindex?|
|Previous:||From: Russell Wallace||Date: 2009-11-28 22:34:11|
|Subject: BUG #5218: Easy strategic feature requests|