Re: Date with time zone

On Saturday 28 November 2009 3:41:42 pm Eduardo Piombino wrote:
> 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)" ?

The problem arises around the dates when DST starts and ends. For instance here,
Washington State USA, Nov 1st was the change over date. This occurred at 2:00
AM in the morning, so on Nov 1st we where in two time zones PDT then PST.
Without a reference to time it makes it hard to keep track.

>
> 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?

I will let the SQL gurus answer this one.

>
> 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.

As stated above time zones only have meaning with respect to date and time
together.

>
> 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.
>

I would suggest searching the archives. There has been discussions in the past
about 'tagged' fields that would track timezones independent of a
time/date/timestamp field.

--
Adrian Klaver
aklaver(at)comcast(dot)net