Thread: Date with time zone

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.

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@comcast.net

I have a database with a schema called ISS.  This is where all of our
application defintions are stored.  We did add 2 contribute modules (citext)
and guid generator and both of these by default went to the public schema.
It is our intent to not allow any access to public by our users.

A few questions

1.  Can I reinstall the contrib modules in the ISS schema only or do they
need to be in the public schema

2.  If they need to stay in the public schema and I don't want to give any
insert, update, delete or select access to public, can I revoke those
privileges and just give execute on the functions that were added by the
contrib module.

3.  If I can reinstall the contrib modules in the application schema, can I
delete the public schema or does it still need to be there and I would just
revoke all except for the superuser id which would be for our installer or
tech support if needed.  We have a separate userid for the security
administrator.  All of the functions that the security administrator needs
are provided by a application module and they will not be directly accessing
the database via a SQL utility at all.

Best Regards


--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax

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 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@comcast.net

Eduardo Piombino <drakorg@gmail.com> writes:
> 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?

It seems pretty ill-defined to me, considering that many jurisdictions
don't switch daylight savings time at local midnight.  How would you
know which zone applied on a DST transition date?

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

Because the SQL committee were smoking something strange that day.
You won't find anybody around here who will defend the existence of
TIME WITH TIME ZONE.  We only put it in for minimal spec compliance.

            regards, tom lane

Speaking of timestamps, I think it would be convenient to have a
single-word alias for "timestamp with time zone". This is the date
type I use almost exclusively and its name is annoyingly big.



On Sat, Nov 28, 2009 at 6:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Eduardo Piombino <drakorg@gmail.com> writes:
>> 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?
>
> It seems pretty ill-defined to me, considering that many jurisdictions
> don't switch daylight savings time at local midnight.  How would you
> know which zone applied on a DST transition date?
>
>> 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?
>
> Because the SQL committee were smoking something strange that day.
> You won't find anybody around here who will defend the existence of
> TIME WITH TIME ZONE.  We only put it in for minimal spec compliance.
>
>                        regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

timestamptz

On Sat, Nov 28, 2009 at 7:25 PM, silly8888 <silly8888@gmail.com> wrote:
> Speaking of timestamps, I think it would be convenient to have a
> single-word alias for "timestamp with time zone". This is the date
> type I use almost exclusively and its name is annoyingly big.
>
>
>
> On Sat, Nov 28, 2009 at 6:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Eduardo Piombino <drakorg@gmail.com> writes:
>>> 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?
>>
>> It seems pretty ill-defined to me, considering that many jurisdictions
>> don't switch daylight savings time at local midnight.  How would you
>> know which zone applied on a DST transition date?
>>
>>> 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?
>>
>> Because the SQL committee were smoking something strange that day.
>> You won't find anybody around here who will defend the existence of
>> TIME WITH TIME ZONE.  We only put it in for minimal spec compliance.
>>
>>                        regards, tom lane
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
When fascism comes to America, it will be intolerance sold as diversity.

On Sat, Nov 28, 2009 at 4:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Eduardo Piombino <drakorg@gmail.com> writes:
>> 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?
>
> It seems pretty ill-defined to me, considering that many jurisdictions
> don't switch daylight savings time at local midnight.  How would you
> know which zone applied on a DST transition date?

Yeah, I think the only reasonable way to define a date with a timezone
would be as some kind of interval, starting at 00:00:00 and going
until 23:59:59.99999 (or < 00:00:00 next day, whichever is more
accurate.  On spring forward / fall back days it would be 23 or 25
hours respectively.  I'm not sure what you'd DO with it though.

> TIME WITH TIME ZONE.  We only put it in for minimal spec compliance.

Yeah, it's kinda twilight zonish to me.

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.

On Sunday 29 November 2009 2:38:43 pm Eduardo Piombino wrote:
> On Sat, Nov 28, 2009 at 8:55 PM, Adrian Klaver <aklaver@comcast.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.


Not really because such an animal does not exist AFAIK. If does it is the
exception.


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

Because this assumes you are in the London time zone. If you are placing bets
from outside the London time zone you need to be aware of the time offset,
because the local time you can place the bet is going to change based on the
time zone in effect. To know what time zone is in effect you need to know the
date.



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

You can referr to date just not with a time zone.

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

Yes, unless it is on a DST transition date. Then it up from local_timezone1 from
time1 to time2 and then from local_timezone2 from time1 to time2.

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

Now you are getting deeper. A "day" has different meanings. It can be an
arbitrarily anchored 24 hr period or it can be midnight to midnight. So
absolute is relative :)

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

Again you are assuming that time outside of UTC is more fixed than it is.
See here for an educational experience:
http://www.twinsun.com/tz/tz-link.htm



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


Which is the difference between those date/time type that have the time zone
modifier (time excepted).

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

I got lost here.

> Basically, back to the subject of this thread "Date with time zone?"
>
> Just some thoughts.
> Comments welcome.
>
> Eduardo.



--
Adrian Klaver
aklaver@comcast.net

On Mon, Nov 30, 2009 at 01:51:33AM -0300, Eduardo Piombino wrote:
> Analysis of the extra complications added by DST's does not add anything,
> yet, to the point I'm trying to make, regardless the lack of such cases in
> practice.

The major problem with timezone support in SQL is that they basically
punt on DST altogether, making it somewhat useless for general use.
(Which is why the timetz type as it is defined by SQL doesn't actually
do what you want.) Saying that you're going to ignore DST in the first
round is ignoring the elephant in the room: you *have* to deal with it.

While your example of 6pm London Time is good, I'm having a hard time
imagining you'd want to store such a value in a database.

> From a technical point of view, that time, 6PM London Time, can be easily
> defined by a "time with time zone" data type, contrary to any other setup
> based on assumptions (such as assigning the default local time zone of where
> the server is to the "time without time zone", or keeping track of the time
> zone on a different data field), with a simple "18:00:00+00" (+00 stands for
> London Time).

Bzzt. +00 is not "London Time", it's UTC. London time is sometimes +01.

> Wouldn't it be nice/elegant to be able to specify that specific day in a
> "date with time zone" format?
> Something like "24/12/2009+00", that would be like adding an offset to both
> start and end time.
> That way, the date itself knows where in the world its being placed
> (London), as an instance of an abstract definition of a date (December
> 24th/2009).

Frankly, I think it's easier and clearer to say the interval from
1261612800 to 1261699200 seconds after 1970-01-01 00:00:00 UTC. That's
at least totally unambiguous, now and into the future. And everybody
can trivially convert it to whatever view they want.

> A day in this context meant midnight to midnight.

That's your definition, but hardly the only useful one.

> Answer me this question then:
> What day is it now?
> You can't answer me Monday, November 30th.
> You should instead ask me: -Where?
> Because the current day will depend on the location, aka, time zone.

Indeed, the question is invalid. Long experience has taught me that
when dealing with times you must strictly seperate the concept of "an
instant in time" and "what your clock says". An instant in time is what
is represented by the "timestamptz" type and is (barring relativity)
universal. What your clock says is what the "timestamp" type gives and
any time I've seen it used to store data it causes grief in the end.
Mainly due to the fact that even with timezone information it's
ambiguous.

If your argument is that what we actually need is an "interval with
time zone" type, then I could possibly agree with you there.

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Sunday 29 November 2009 8:51:33 pm Eduardo Piombino wrote:
>
> Just sharing some thoughts.
> 1. That current "date" datatype is actually an abstract definition of a
> time range. Since it is not localized (put in any time zone), it defines a
> time range going from 00:00:00 hs to 23:59:59.9999 hs of that specific
> non-localized day.
>
> 2. That it does not exist any other abstract "localizable" time range data
> type that i know of, similar to date, such as "month", or even "year".
>
> Because again, if they existed, they would again, need to keep track of
> time zones if they are to be used in multi time zones setups.
>
> I mean, It can be December on a timezone, and January on the next one.
> And the same with the years, it can be 2009 in a time zone, and 2010 in the
> next one.
> The exact same fundamental issue that moved me to bring this subject here.
>
> So I kinda feel the need of specifying a time zone when talking about a
> specific date, a specific month, or a specific year, since all of them
> denote a time range, and that time range can differ according to what time
> zone you are in.
>
> Answer me this question then:
> What day is it now?
> You can't answer me Monday, November 30th.
> You should instead ask me: -Where?
> Because the current day will depend on the location, aka, time zone.
>
> Then, if you want to state a complete reference to specific date, in a
> specific location, you should be able, imo, to specify it along with a time
> zone.
>

Basically you want the time zone info to be informational not binding. The
problem with that is gives the impression that the data type is more complete
then it is i.e the problem with "time with time zone". It also means an extra
level of sanity checks when trying to to do date/time math, to determine
whether the values can actually be operated on. I don't see that happening.

> >
> > --
> > Adrian Klaver
> > aklaver@comcast.net



--
Adrian Klaver
aklaver@comcast.net

On Mon, Nov 30, 2009 at 7:22 AM, Martijn van Oosterhout <kleptog@svana.org> wrote: