Am Donnerstag, 11. Oktober 2007 schrieb Gregory Stark:
Thinking of it as UTC is the wrong way to think about it. A birth occurred
at a specific moment in time. You want to record that precise moment, not
what it happened to show on the clock at the time. If the clock turns out
to have been in
On 10/10/07, Tom Lane [EMAIL PROTECTED] wrote:
Trevor Talbot [EMAIL PROTECTED] writes:
Actually, what I meant at least (not sure if others meant it), is
storing the value in the timezone it was entered, along with what zone
that was. That makes the value stable with respect to the zone it
Trevor Talbot wrote:
Thinking that it might have had out of date zone rules brings up an
interesting scenario though. Consider a closed (no networking or
global interest) filing system in a local organization's office, where
it's used to record the minutes of meetings and such via human input.
Tom Lane wrote:
As an example, timestamptz '2007-01-01 00:00 -05' + interval '6 months'
must yield 2007-07-01 00:00 -05 according to the spec, AFAICS; but most
people living in the EST5EDT zone would prefer to get midnight -04.
There are probably some folk in South America who'd prefer midnight
On 10/11/07, Magne Mæhre [EMAIL PROTECTED] wrote:
Trevor Talbot wrote:
Thinking that it might have had out of date zone rules brings up an
interesting scenario though. Consider a closed (no networking or
global interest) filing system in a local organization's office, where
it's used to
Trevor Talbot [EMAIL PROTECTED] writes:
On 10/11/07, Magne M=E6hre [EMAIL PROTECTED] wrote:
Trevor Talbot wrote:
That situation might sound a bit contrived, but I think the real point
is that even for some records of observed times, the local time is the
authoritative one, not UTC.
...and
Tom Lane [EMAIL PROTECTED] writes:
Trevor Talbot [EMAIL PROTECTED] writes:
On 10/11/07, Magne M=E6hre [EMAIL PROTECTED] wrote:
Trevor Talbot wrote:
That situation might sound a bit contrived, but I think the real point
is that even for some records of observed times, the local time is the
On 10/11/07, Gregory Stark [EMAIL PROTECTED] wrote:
Tom Lane [EMAIL PROTECTED] writes:
Trevor Talbot [EMAIL PROTECTED] writes:
On 10/11/07, Magne Mæhre [EMAIL PROTECTED] wrote:
Trevor Talbot wrote:
That situation might sound a bit contrived, but I think the real point
is that even for
=?ISO-8859-1?Q?Magne_M=E6hre?= [EMAIL PROTECTED] writes:
Correct me if I'm wrong, but IIRC there is no universally accepted
canonical list of time zone names (labels).
Yeah; we have an agreed-on list of names for the purposes of PG, namely
the names shown by pg_timezone_names, but that list
Trevor Talbot [EMAIL PROTECTED] writes:
2) Specific moment in time
(i.e. stored in UTC which is unaffected by time zone rules)
3) Specified time of day in specified time zone
(equivalent to #2 except when the time zone rules change)
Surely #2 is a must-have. There has to be a data
On 10/11/07, Gregory Stark [EMAIL PROTECTED] wrote:
Trevor Talbot [EMAIL PROTECTED] writes:
While I agree that UTC storage is definitely a needed option, I was
trying to point out in the scenario above that sometimes an event
recorded at a specific moment in time *is* local time. Birth
Trevor Talbot [EMAIL PROTECTED] writes:
Neither is the birth certificate. The recorded, legal time of the
birth is the one that was written down. If it doesn't happen to match
an international notion of current time, that's unfortunate, but it's
not subject to arbitrary changes later. Even
On 10/11/07, Tom Lane [EMAIL PROTECTED] wrote:
Trevor Talbot [EMAIL PROTECTED] writes:
Neither is the birth certificate. The recorded, legal time of the
birth is the one that was written down. If it doesn't happen to match
an international notion of current time, that's unfortunate, but
Trevor Talbot [EMAIL PROTECTED] writes:
I don't know if there have ever been retroactive changes to DST laws
we could look at, but I could easily see a change like that affecting
some things and not others.
Even a politician would hardly be silly enough to make a retroactive
DST law change.
Peter Eisentraut [EMAIL PROTECTED] writes:
We are not considering an interval scheduling system, we are considering a
database system. Such a system should have the basic property that if you
store A, it will read out as A.
I'm not sure that I think this sort of rigid thinking works very
* Peter Eisentraut [EMAIL PROTECTED] [071010 09:58]:
If we make an appointment at 12-November-2007 at 10:00 CET (winter time) and
next week those in charge decide to postpone the change to winter time from
28-October-2007 to 25-November-2007, what becomes of the appointment? Do we
still
Am Mittwoch, 10. Oktober 2007 schrieb Tom Lane:
I'm not sure that I think this sort of rigid thinking works very well in
the wonderland that is date/time behavior. When the rules of the game
(ie, DST laws) are changing underneath you, who is to say exactly what
reading out as A means?
Aidan Van Dyk [EMAIL PROTECTED] writes:
* Peter Eisentraut [EMAIL PROTECTED] [071010 09:58]:
If we make an appointment at 12-November-2007 at 10:00 CET (winter time) and
next week those in charge decide to postpone the change to winter time from
28-October-2007 to 25-November-2007, what
Tom Lane wrote:
Exactly ... there is more than one right answer here. The answer that
PG's TIMESTAMP WITH TIME ZONE code deems to be right is that UTC is
reality. That's a definition that is indeed useful for a wide variety
of real-world problems. In a lot of cases where it's not so useful,
On 10/10/07, Tom Lane [EMAIL PROTECTED] wrote:
The arguments that have been made for storing a zone along with the UTC
value seem to mostly boil down to it should present the value the same
way I entered it, but if you accept that argument then why do we have
DateStyle? If it's OK to
Trevor Talbot [EMAIL PROTECTED] writes:
Actually, what I meant at least (not sure if others meant it), is
storing the value in the timezone it was entered, along with what zone
that was. That makes the value stable with respect to the zone it
belongs to, instead of being stable with respect
Am Mittwoch, 10. Oktober 2007 schrieb Tom Lane:
Peter's example of a future appointment time is a possible
counterexample, but as observed upthread it's hardly clear which
behavior is more desirable in such a case.
Whereas the most realistic solution to my example might be, the parties
Trevor Talbot wrote:
, what I meant at least (not sure if others meant it), is
storing the value in the timezone it was entered, along with what zone
that was. That makes the value stable with respect to the zone it
belongs to, instead of being stable with respect to UTC. When DST
rules
=?ISO-8859-1?Q?Magne_M=E6hre?= [EMAIL PROTECTED] writes:
I would suggest that the WITH TIMEZONE elements are converted to UTC when
inserted into the database. Since all operations on it are based on
its UTC form, it's most efficient ( I believe) if the data is stored that
way. To be
On Wed, Oct 10, 2007 at 12:23 PM, in message
[EMAIL PROTECTED], Magne
Mæhre [EMAIL PROTECTED] wrote:
An interesting observation is that,
as far as I can tell, the original time zone is only applied when
casting
the element to a string. Apart from that, it's not used.
It's been a while
On Wed, Oct 10, 2007 at 2:51 PM, in message
[EMAIL PROTECTED], Kevin Grittner
[EMAIL PROTECTED] wrote:
On Wed, Oct 10, 2007 at 12:23 PM, in message
[EMAIL PROTECTED], Magne
Mæhre [EMAIL PROTECTED] wrote:
An interesting observation is that,
as far as I can tell, the original time zone is
Kevin Grittner [EMAIL PROTECTED] writes:
It's been a while since I looked at it, but my recollection is that
much of the standard date/time math which people assert can't handle
practical use cases do work if the timestamps and times WITH TIME
ZONE have a time zone in the offset-from-UTC
On 10/8/07, Bruce Momjian [EMAIL PROTECTED] wrote:
I had a thought a week ago. If we update the time zone database for
future dates, and you have a future date/time stored, doesn't the time
change when the time zone database changes.
For example if I schedule an appointment in New Zealand
I wrote:
On 10/8/07, Bruce Momjian [EMAIL PROTECTED] wrote:
I had a thought a week ago. If we update the time zone database for
future dates, and you have a future date/time stored, doesn't the time
change when the time zone database changes.
For example if I schedule an appointment in
Trevor Talbot wrote:
Actually, I'm used to knowing how PostgreSQL does it, but looking at
things again I remember some confusion I had when first encountering
the timestamp types. I don't know what the SQL Standard says; is the
implication that timestamp with time zone actually stores the
Am Dienstag, 9. Oktober 2007 schrieb Magne Mæhre:
SQL itself doesn't say anything how the data element should be stored,
only how it should be operated upon. It do, however,say that a
datetime/time WITH TIME ZONE represents the time in UTC (SQL 2003,
§4.3). All operations on the element are
On 10/9/07, Peter Eisentraut [EMAIL PROTECTED] wrote:
Independent of what any specification might say, however, the currently
implemented behavior is clearly wrong in my mind and needs to be fixed.
I don't think it's wrong, just a particular choice. As an example,
consider an interval
On Mon, Oct 8, 2007 at 10:48 PM, in message
[EMAIL PROTECTED], Bruce Momjian [EMAIL PROTECTED] wrote:
I had a thought a week ago. If we update the time zone database for
future dates, and you have a future date/time stored, doesn't the time
change when the time zone database changes.
For
On Tue, Oct 9, 2007 at 6:49 AM, in message
[EMAIL PROTECTED], Magne
Mæhre [EMAIL PROTECTED] wrote:
Interestingly, if you cast a TIMESTAMP WITH TIME ZONE to a character
value, it should be converted with the _original_ time zone value
(SQL
2003, *5.8) _unless_ you specify AT LOCAL.
A
Am Dienstag, 9. Oktober 2007 schrieb Trevor Talbot:
I don't think it's wrong, just a particular choice. As an example,
consider an interval scheduling system that handles everything in
absolute time (UTC), but uses local time as a convenience.
We are not considering an interval scheduling
On Tue, Oct 09, 2007 at 05:04:39PM +0200, Peter Eisentraut wrote:
We are not considering an interval scheduling system, we are considering a
database system. Such a system should have the basic property that if you
store A, it will read out as A. The money type is similarly buggy: if you
Trevor Talbot wrote:
I wrote:
On 10/8/07, Bruce Momjian [EMAIL PROTECTED] wrote:
I had a thought a week ago. If we update the time zone database for
future dates, and you have a future date/time stored, doesn't the time
change when the time zone database changes.
For example if
On Tue, Oct 9, 2007 at 12:11 PM, in message
[EMAIL PROTECTED], Bruce Momjian [EMAIL PROTECTED] wrote:
Trevor Talbot wrote:
Actually, I'm used to knowing how PostgreSQL does it, but looking at
things again I remember some confusion I had when first encountering
the timestamp types. I don't
Kevin Grittner wrote:
Probably, but we need a lot more than that to conform to the standard
and to avoid surprising behavior. The first of the two statements
below is valid ANSI syntax to add one day to the current moment.
That's the lack of standard interval support, which is an entirely
39 matches
Mail list logo
