Re: Date with time zone - Mailing list pgsql-general
| From | Eduardo Piombino |
|---|---|
| Subject | Re: Date with time zone |
| Date | |
| Msg-id | e24c1d9d0911292051p409bdd3t2de2baaa12272921@mail.gmail.com Whole thread Raw |
| In response to | Re: Date with time zone (Adrian Klaver <aklaver@comcast.net>) |
| Responses |
Re: Date with time zone
Re: Date with time zone |
| List | pgsql-general |
On Sun, Nov 29, 2009 at 8:23 PM, Adrian Klaver <aklaver@comcast.net> 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.
I don't really care about the local time from where the bets are being taken.
The server is located in London, configured with the London Time offset, and it is this server who accepts or rejects the bets.
So if you place a bet after 6PM London Time, regardless it's 10AM in your country, it will still be rejected.
It is also very clearly stated in the web page that the bets are taken until 6PM London Time.
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).
You can even have a server setup anywhere in the world, with any arbitrary time zone (for the server) and still be able to take bets until 6PM London Time only having specified the same value for the deadline as before "18:00:00+00" (time with time zone).
I find it particularly more elegant to use this data type if available, you kill two birds at once.
Yes, but you would miss the important information of the time zone for that specific day.
What if you have a promotion and you would like to say "Guys from all over the world, during December 24th (London Time), you will be able to purchase at half the price.".
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).
A day in this context meant midnight to midnight.
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.
Regards.
On Sunday 29 November 2009 2:38:43 pm Eduardo Piombino wrote:Not really because such an animal does not exist AFAIK. If does it is the
> 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.
exception.
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.
> >Because this assumes you are in the London time zone. If you are placing bets
> > 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.
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 thedate.
I don't really care about the local time from where the bets are being taken.
The server is located in London, configured with the London Time offset, and it is this server who accepts or rejects the bets.
So if you place a bet after 6PM London Time, regardless it's 10AM in your country, it will still be rejected.
It is also very clearly stated in the web page that the bets are taken until 6PM London Time.
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).
You can even have a server setup anywhere in the world, with any arbitrary time zone (for the server) and still be able to take bets until 6PM London Time only having specified the same value for the deadline as before "18:00:00+00" (time with time zone).
I find it particularly more elegant to use this data type if available, you kill two birds at once.
You can referr to date just not with a time zone.
>
> 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.
Yes, but you would miss the important information of the time zone for that specific day.
What if you have a promotion and you would like to say "Guys from all over the world, during December 24th (London Time), you will be able to purchase at half the price.".
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).
Yes, unless it is on a DST transition date. Then it up from local_timezone1 from
>
> 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.
time1 to time2 and then from local_timezone2 from time1 to time2.Now you are getting deeper. A "day" has different meanings. It can be an
>
> 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.
arbitrarily anchored 24 hr period or it can be midnight to midnight. So
absolute is relative :)
A day in this context meant midnight to midnight.
Again you are assuming that time outside of UTC is more fixed than it is.
>
> 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.
See here for an educational experience:
http://www.twinsun.com/tz/tz-link.htmWhich is the difference between those date/time type that have the time zone
>
> 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.
modifier (time excepted).I got lost here.
>
> 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?
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.
Regards.
--
> Basically, back to the subject of this thread "Date with time zone?"
>
> Just some thoughts.
> Comments welcome.
>
> Eduardo.
pgsql-general by date: