Re: Date with time zone

On Sat, Nov 28, 2009 at 8:55 PM, Adrian Klaver <aklaver(at)comcast(dot)net> wrote:

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

For the sake of simplicity I would like to leave the DST's modifications off
the discussion, just for now.
Considering a fixed setup, where every country has a fixed time zone (or
many, but fixed), I will try to make my point. If I we can all agree on the
point at a fixed setup, I'm more than willing to get into a more complex
scenario and just then introduce summer times, etc. If you are ok with it.

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

I'm not quite sure about this. For example, if you want to say "I will
accept bets until 6 o'clock (London Time), every day". How does that time
reference need to be tied to a specific date?. It's just a reference to a
time in the day, by itself. Plus a time zone, because 6 o'clock by itself is
also not sufficient, it still needs a time zone reference. It's also not a
timestamp, nor a date, its just a time (with time zone tracking
capabilities). And that's where I see that "time with time zone" is a good,
and the one data type to use, IMO.

>
> >
> > 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.
>
>
>
I will, thanks for the reference.

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

Going back to the date with datetime, follow me with this reasoning:
If you consider a specific date (in a specific time zone) defined as a
specific starting point in time (absolute and universal), and a specific
ending point in time, one could say that a date is a period of time ranging
from 00:00 hs to 23:59:59.9999 (just as some other member list suggested,
with which I totally agree), both times tied to a specific time zone.

One could then say that, "Dec 19th (London Time)" goes from "Dec 19th
00:00:00 (London Time)" to "Dec 19th 23:59:59.99999... (London Time)". So,
making a reference to Dec 19th (London Time) would make sense as it refers
to a specific absolute time frame, or range. This range, could be (or not,
but that is not the point) useful for some practical situations, but the
impossibility to be able to state a reference to a date, without recurring
to some tweaks or patches is sthing that at the least, bugs me a little.

For instance, if you have a server and you want to tell someone it will be
up tomorrow, all day long, with that single reference, your buddy knows
exactly that it will be up from 00:00:00 of that day (Your Local Time), to
23:59:59.9999 that day (also in your local time), at least in theory,
according to what u said.

And "tomorrow" in that sentence, works as an implicit reference to a day,
with a time zone, which combined, references a specific absolute time range.

Maybe another thing that goes against this, is that there would seem to be
no reasonable applications for that data type, but I recall having the need
for this once, and I think that was the first time I would have liked to be
able to specify a date along with a time zone. But again, apart from the
possible applications, I still think it is a logic data type to have. To be
able to reference a specific date, in a specific country, or time zone.

Another example that comes into my mind is that you may have some coupons,
that u can only use on "Dec 19th" (London Time).
Then you have others that u can only use on "Dec 20th" (London Time).

You could very well design the db model with a
from "timestamp with time zone"
to "timestamp with time zone"

and you would be done, or you could simply define a single field
day "date with time zome", to be able to reference the same range in a
single field.

If you agree with me on the reasoning behind all the latter cases, we can
start analyzing the DST complications, which at the moment just seemed to
add some complexity that would eventually undermine my attempts to keep my
point the simplest I could, to be sure everyone agreed at least on that.

However, I must say, that I find a pattern on what I've just said.
Why limit the tracking of timezones to a specific day, why not to specif
months, in the end they are also time ranges.

January (London Time) actually goes from "Jan 1st 00:00:00 (London Time)" to
"Jan 31st 23:59:59.99999 (London Time)".
But is different from January (Brasilia Time) which goes from "Jan 1st
00:00:00" to "Jan 31st 23:59:59.999999" (Brasilia Time).

So with this reasoning, I guess months should also be able to go along a
timezone reference, and that just starts to sound a little ridiculous. Don't
ask me why.

As a conclusion to all this, sorry guys if I am boring you ! I would almost
conclude that:

We have two kind of time ranges:

First of them, ABSTRACT time ranges:
This category includes the abstract definitions of time ranges, for example:
1. A day ranges from 00:00:00 to 23:59:59.99999, independently of whichever
day you are on. Its just a definition.
2. A month, lets say, January, ranges from "January 1st 00:00:00" to
"January 31st 23:59:59.99999", on any given time zone. Its just a
definition.
3. A year is also a range that goes from "January 1st 00:00:00" to "Dec 31st
23.59:59.9999", on any given time zone. Its just a definition.

But when you are talking about a specific time range in a specific location,
it stops from being an abstract definition, and becomes an INSTANCE of that
time range, and as such, it starts to keep track of its time zone: For
example.

1. "Jan 1st" for someone living in London, is a time range going from "Jan
1st 00:00:00 (London Time)" to "Jan 1st 23:59:59.9999 (London Time)"
2. "January" for someone living in London, is a time range going from "Jan
1st 00:00:00 (London Time)" to "Jan 31st 23:59:59.9999 (London Time)".
3. The year 2009 for some living in London, is a time range going from "Jan
1st 00:00:00 (London Time)" to "Dec 31st 23:59:59.9999 (London Time)".

So, when speaking of time ranges, i see there are 2 kind of them, ABSTRACT,
and INSTANCES, where abstracts are just the definitions, and instances the
concrete implementations of an abstract, put in a specific time zone.

What I see then, is a lack of support for localized time range data types,
that would be:
1. date with time zone (not supported).
2. month with time zone (but this datatype doesnt even exist in its abstract
form)
3. year with time zone (but this datatype doesnt even exist in its abstract
form).
4. etc.

So, maybe there is some coherence it not "localizing" a date, because no
other time range is being also "localized".
Maybe the discussion should turn into:

Should time ranges be localized? or even normalized?
Some normalization for ranges already exist, since there is already a "date"
datatype. Which is nothing more than an abstract definition of a range that
goes from 00:00:00 of that specific date, to 23:59:59.9999.

If such normalization exists, should they also be available in a localized
form?
Basically, back to the subject of this thread "Date with time zone?"

Just some thoughts.
Comments welcome.

Eduardo.