Re: Date with time zone

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 <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
> 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
> aklaver(at)comcast(dot)net
>