Thread: timestamp with/without time zone
Let's switch 'timestamp with time zone' back to 'timestamp'. This just makes no sense. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> Let's switch 'timestamp with time zone' back to 'timestamp'. This just > makes no sense. I wasn't following that discussion. Why would we have a timestamp with no timezone anyway? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
We don't have it. Its just that it is misleading to have 'timestamp with time zone' as description of this type, when 'timestamp without time zone' does not exist. (Actually, if you try to create a field as 'timestamp without time zone', you get timestamp anyway). Since you can't have 'without', why mention that the type has a time zone? -alex On Mon, 18 Jun 2001, Bruce Momjian wrote: > > Let's switch 'timestamp with time zone' back to 'timestamp'. This just > > makes no sense. > > I wasn't following that discussion. Why would we have a timestamp with > no timezone anyway? > >
> We don't have it. Its just that it is misleading to have 'timestamp with > time zone' as description of this type, when 'timestamp without time zone' > does not exist. (Actually, if you try to create a field as 'timestamp > without time zone', you get timestamp anyway). Since you can't have > 'without', why mention that the type has a time zone? > Totally agree. I see in psql's \dT: time with time zone | hh:mm:ss, ANSI SQL timetimestamp with time zone | date and time Looks like 'time' has a similar problem. Let me know if I can help. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Let's switch 'timestamp with time zone' back to 'timestamp'. This just
>> makes no sense.
> I wasn't following that discussion. Why would we have a timestamp with
> no timezone anyway?
The discussion isn't about what the datatype *does*, but only about what
it's *called*.
We currently transform requests for "timestamp", "timestamp with
timezone", and "timestamp without timezone" into the same "timestamp"
datatype. This is fine by me. However, I think that that datatype
should display as just "timestamp" in psql displays and pg_dump output.
The datatype does not act exactly the same as SQL92's "timestamp with
timezone", so it seems to me that displaying it that way just confuses
people.
However, Thomas disagreed when last heard from...
regards, tom lane
> >> Let's switch 'timestamp with time zone' back to 'timestamp'. This just
> >> makes no sense.
> > I wasn't following that discussion. Why would we have a timestamp with
> > no timezone anyway?
To be "compatible" with the SQL9x brain damage. Support of standards in
this area is a big step backwards in functionality.
> The discussion isn't about what the datatype *does*, but only about what
> it's *called*.
I'd be supportive (and willing to consider doing the work ;) for one of
several options:
1) implement "timestamp without timezone", moving the current
implementation to be "timestamp with time zone".
2) implement true SQL9x "timestamp with time zone", and move the current
implementation back to "datetime". I'd consider this a real pita since
the only reason I moved it from that originally is that *no one*, over a
period of years, was willing to take responsibility for a compliant
"timestamp" implementation. We all agreed to the original change, and I
raised this as an issue back then. Oh, and SQL9x "timestamp with
timezone" is truly brain damaged, and you should be sure that this is
what you would really want. Really really want.
3) continue the status quo, with modest relabeling of the current
capabilities.
> We currently transform requests for "timestamp", "timestamp with
> timezone", and "timestamp without timezone" into the same "timestamp"
> datatype. This is fine by me. However, I think that that datatype
> should display as just "timestamp" in psql displays and pg_dump output.
> The datatype does not act exactly the same as SQL92's "timestamp with
> timezone", so it seems to me that displaying it that way just confuses
> people. However, Thomas disagreed when last heard from...
Hmm. Any solution will have some confusion, so it really is not clear
which labeling path is preferable. Maybe even to those who think it is
clear ;)
SQL9x "timestamp" has no notion of time zones. PostgreSQL "timestamp"
does. This is likely the reason for the current labeling scheme (at
least in pgdump). This also lays the groundwork for more seamless
upgrade paths later when a "time zone free" timestamp type might be
available.
- Thomas
> 3) continue the status quo, with modest relabeling of the current > capabilities. > > Hmm. Any solution will have some confusion, so it really is not clear > which labeling path is preferable. Maybe even to those who think it is > clear ;) > > SQL9x "timestamp" has no notion of time zones. PostgreSQL "timestamp" > does. This is likely the reason for the current labeling scheme (at > least in pgdump). This also lays the groundwork for more seamless > upgrade paths later when a "time zone free" timestamp type might be > available. Very few people know the standards stuff so it seems we should just call it timestamp and do the best we can. Basically by mentioning "with timezone" we are making the standards people happy but confusing our users. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Very few people know the standards stuff so it seems we should just call
> it timestamp and do the best we can. Basically by mentioning "with
> timezone" we are making the standards people happy but confusing our
> users.
I don't believe we're making any standards-lovers happy either, because
the datatype in question *is* *not* SQL9x's TIMESTAMP WITH TIME ZONE.
Given that no one actually wants to change its behavior to conform to
either of the standard's datatypes, ISTM that calling it something
different from either of those two is the appropriate path.
At some point (if someone is foolish enough to want to implement the
spec's semantics) we might have three distinct datatypes called
timestamp, timestamp with time zone, and timestamp without time zone,
with the first of these (the existing type) being the recommended
choice. What we have at the moment is that lacking implementations
for the last two, we map them into the first one. That doesn't seem
unreasonable to me. But to have a clean upgrade path from one to three
types, we need to be sure we call the existing type what it is, and not
mislabel it as one of the spec-compliant types.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Very few people know the standards stuff so it seems we should just call > > it timestamp and do the best we can. Basically by mentioning "with > > timezone" we are making the standards people happy but confusing our > > users. > > I don't believe we're making any standards-lovers happy either, because > the datatype in question *is* *not* SQL9x's TIMESTAMP WITH TIME ZONE. > Given that no one actually wants to change its behavior to conform to > either of the standard's datatypes, ISTM that calling it something > different from either of those two is the appropriate path. > > At some point (if someone is foolish enough to want to implement the > spec's semantics) we might have three distinct datatypes called > timestamp, timestamp with time zone, and timestamp without time zone, > with the first of these (the existing type) being the recommended > choice. What we have at the moment is that lacking implementations > for the last two, we map them into the first one. That doesn't seem > unreasonable to me. But to have a clean upgrade path from one to three > types, we need to be sure we call the existing type what it is, and not > mislabel it as one of the spec-compliant types. I am confused what you are suggesting here. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am confused what you are suggesting here.
*** src/backend/utils/adt/format_type.c.orig Wed May 23 18:10:19 2001
--- src/backend/utils/adt/format_type.c Mon Jun 18 21:41:53 2001
***************
*** 178,184 **** break; case TIMESTAMPOID:
! buf = pstrdup("timestamp with time zone"); break; case VARBITOID:
--- 178,184 ---- break; case TIMESTAMPOID:
! buf = pstrdup("timestamp"); break; case VARBITOID:
Clear enough?
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I am confused what you are suggesting here.
>
> *** src/backend/utils/adt/format_type.c.orig Wed May 23 18:10:19 2001
> --- src/backend/utils/adt/format_type.c Mon Jun 18 21:41:53 2001
> ***************
> *** 178,184 ****
> break;
>
> case TIMESTAMPOID:
> ! buf = pstrdup("timestamp with time zone");
> break;
>
> case VARBITOID:
> --- 178,184 ----
> break;
>
> case TIMESTAMPOID:
> ! buf = pstrdup("timestamp");
> break;
Yes, this is exactly what I would suggest. In fact, \dT shows this long
text and it is making some of the lines too long.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Thomas Lockhart writes: > SQL9x "timestamp" has no notion of time zones. PostgreSQL "timestamp" > does. AFAICT, it does not. The value is stored in UTC (more or less) and is converted to the local time zone for display. But a data type is defined in terms of storage, not display. In fact, if you use a language binding that converts PostgreSQL values directly to native data types, then the time zone never appears. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Peter Eisentraut <peter_e@gmx.net> writes:
> Thomas Lockhart writes:
>> SQL9x "timestamp" has no notion of time zones. PostgreSQL "timestamp"
>> does.
> AFAICT, it does not. The value is stored in UTC (more or less) and is
> converted to the local time zone for display. But a data type is defined
> in terms of storage, not display.
I think Thomas' point is mainly a syntactic one, that our timestamp type
will accept and display timezones --- which makes it compatible at the
I/O level with SQL-style TIMESTAMP WITH TIME ZONE. But I don't find
that argument very persuasive. An app that is expecting SQL-compliant
handling of the zone info will still be broken, only in subtle
hard-to-find ways instead of nice simple obvious ways. IMHO we don't
support TIMESTAMP WITH TIME ZONE, and we really oughtn't give people the
impression that we do. Whether what we have is better than the spec's
definition is irrelevant here; the point is that it's different.
regards, tom lane
Thomas, can we change the description to just 'timestamp'? > Peter Eisentraut <peter_e@gmx.net> writes: > > Thomas Lockhart writes: > >> SQL9x "timestamp" has no notion of time zones. PostgreSQL "timestamp" > >> does. > > > AFAICT, it does not. The value is stored in UTC (more or less) and is > > converted to the local time zone for display. But a data type is defined > > in terms of storage, not display. > > I think Thomas' point is mainly a syntactic one, that our timestamp type > will accept and display timezones --- which makes it compatible at the > I/O level with SQL-style TIMESTAMP WITH TIME ZONE. But I don't find > that argument very persuasive. An app that is expecting SQL-compliant > handling of the zone info will still be broken, only in subtle > hard-to-find ways instead of nice simple obvious ways. IMHO we don't > support TIMESTAMP WITH TIME ZONE, and we really oughtn't give people the > impression that we do. Whether what we have is better than the spec's > definition is irrelevant here; the point is that it's different. > > regards, tom lane > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Thomas Lockhart <lockhart@fourpalms.org> writes:
> The description in pg_dump was chosen to assist with a transition in the
> next version of PostgreSQL to having available a true "no time zone"
> timestamp, leaving the current implementation as the "time zone aware"
> type. I'm concerned about changing the current choice in the absence of
> thought about this issue.
I already commented what I thought about this: the current type is not
either of the SQL-compatible timestamp types, and if we want to support
the SQL-compatible semantics then we need three types, not two.
> On a related note, I've been thinking about removing the following
> features from our current "timestamp":
> o timestamp 'invalid' - an interesting concept which might actually be
> useful for the original abstime type since it has such a limited range,
> but not generally useful for timestamp. I'd suggesting leaving it in for
> abstime, at least for now.
> o timestamp 'current' - another interesting concept not likely used by
> anyone, and causing conniptions for our optimizer (one cannot cache
> results for datasets containing this value).
I believe everyone already agreed that 'current' should be removed.
'invalid' seems somewhat redundant with NULL, so I wouldn't object to
taking it out; on the other hand, is it hurting anything? Also, it
seems a bad idea to remove it from timestamp if we leave it in abstime;
you shouldn't have to worry that casting abstime up to timestamp might
fail.
regards, tom lane
> Thomas, can we change the description to just 'timestamp'?
Sure, we can do anything we want. I don't agree with all of the points
raised, and in particular disagree with the characterization of our
current "timestamp" type as having no concept of time zones, although it
is true that it has no concept of "sticky time zones" which travel with
the data value.
The description in pg_dump was chosen to assist with a transition in the
next version of PostgreSQL to having available a true "no time zone"
timestamp, leaving the current implementation as the "time zone aware"
type. I'm concerned about changing the current choice in the absence of
thought about this issue.
istm that if we dump timestamps which have time zone fields (which at
the moment all do), trying to read them back in as plain-vanilla SQL92
"timestamp" might result in an error condition, leading to dump/restore
problems. Or maybe it would be appropriate for a "time zone free" type
to just ignore time zone info in input? If so, upgrading wouldn't be as
big an issue, and just the upgraded schema would need to be
considered...
On a related note, I've been thinking about removing the following
features from our current "timestamp":
o timestamp 'invalid' - an interesting concept which might actually be
useful for the original abstime type since it has such a limited range,
but not generally useful for timestamp. I'd suggesting leaving it in for
abstime, at least for now.
o timestamp 'current' - another interesting concept not likely used by
anyone, and causing conniptions for our optimizer (one cannot cache
results for datasets containing this value).
Comments?
- Thomas
> I already commented what I thought about this: the current type is not
> either of the SQL-compatible timestamp types, and if we want to support
> the SQL-compatible semantics then we need three types, not two.
Right, that was clear even to me ;)
We were on that path for quite some time. I volunteered to move the
datetime type to become timestamp since *no one* was interested in
implementing timestamp properly. There was extensive (or at least
complete) discussion at the time.
Per Date and Darwen (and common sense) the SQL9x date/time time zone
support is fundamentally flawed, and clearly leads to deep trouble in
trying to operate a database across time zones or national boundaries.
PostgreSQL had strongly influenced SQL standards in the past (e.g. data
type extensibility) and imho our current implementation is the way the
standard should have read.
> I believe everyone already agreed that 'current' should be removed.
> 'invalid' seems somewhat redundant with NULL, so I wouldn't object to
> taking it out; on the other hand, is it hurting anything? Also, it
> seems a bad idea to remove it from timestamp if we leave it in abstime;
> you shouldn't have to worry that casting abstime up to timestamp might
> fail.
I wouldn't worry about that, since we can now return NULL in the
translation of abstime to timestamp. otoh we could choose to do the same
for abstime itself, so 'invalid' is not fundamentally necessary for that
type anymore either.
- Thomas
> > I believe everyone already agreed that 'current' should be removed. > > 'invalid' seems somewhat redundant with NULL, so I wouldn't object to > > taking it out; on the other hand, is it hurting anything? Also, it > > seems a bad idea to remove it from timestamp if we leave it in abstime; > > you shouldn't have to worry that casting abstime up to timestamp might > > fail. > > I wouldn't worry about that, since we can now return NULL in the > translation of abstime to timestamp. otoh we could choose to do the same > for abstime itself, so 'invalid' is not fundamentally necessary for that > type anymore either. Is this a TODO item? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > I already commented what I thought about this: the current type is not > > either of the SQL-compatible timestamp types, and if we want to support > > the SQL-compatible semantics then we need three types, not two. > > Right, that was clear even to me ;) > > We were on that path for quite some time. I volunteered to move the > datetime type to become timestamp since *no one* was interested in > implementing timestamp properly. There was extensive (or at least > complete) discussion at the time. > > Per Date and Darwen (and common sense) the SQL9x date/time time zone > support is fundamentally flawed, and clearly leads to deep trouble in > trying to operate a database across time zones or national boundaries. > PostgreSQL had strongly influenced SQL standards in the past (e.g. data > type extensibility) and imho our current implementation is the way the > standard should have read. I believe the issue here was how do we describe the TIMESTAMP data type, as TIMESTAMP or TIMESTAMP WITH TIMEZONE. I thought people were proposing the former. Thomas, did you express a preference? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Is this a TODO item?
Sure, but I'd hate to have all of these individual items showing up as
separate things in some ToDo list, since it won't paint a coherent
picture of where things are headed.
I'm planning on doing some work on timestamp, which will include:
o support for "ISO variants" on input, including embedded "T" preceeding
the time fields
o deprecation of 'current' (holdover from Original Postgres)
o deprecation of 'invalid' for timestamp at least (holdover from
Original Postgres)
o (probably) deprecation of "ignored fields" if the value not explicitly
recognized (holdover from Original Postgres)
o resolution of the SQL99 vs SQL-useful timestamp and timestamp with
time zone issues
The latter has two possible outcomes (maybe more):
a) we keep the current timestamp implementation as either timestamp or
timestamp with time zone, and implement the other as a new type with
much common underlying code
b) we roll back decisions made a few years ago, and have "SQL-useful
timestamp" become datetime, leaving timestamp with time zone and
timestamp with slavish SQL99 compliance as undersupported, ineffective
and near-useless data types (an overstatement for simple timestamp, but
not for timestamp with time zone).
For those who haven't used a fully compliant timestamp with time zone
(and most haven't, since it is brain damaged) the time zone is specified
as a single offset from GMT. No provisions for DST, etc etc.
The current identification of timestamp as "timestamp with time zone"
was to prepare for implementation of a "no time zone anywhere" timestamp
in 7.2. The current timestamp would become "timestamp with time zone",
with time zone support substantially enhanced from SQL99 specs. I'll
speak for the silent majority to claim that these enhancements are
useful. They are likely compatible enough (or could be) to pass SQL9x
compliance testing, unless that testing includes cases which try to
enforce the worst aspects of the standard.
Hmm, now that I look at it again, SQL99 timestamp with time zone may not
be too far away from our current timestamp, except for issues concerning
UTC vs local time and probably some other details of formatting and
behavior (e.g. allowed date ranges; we allow more).
It appears that SQL99 timestamp with time zone outputs as UTC (which is
how it is stored internally already) so the standard is missing the
notion of representing time zones in the output of a timestamp or
timestamp with time zone type. This is not as horrendous as SQL92 or as
described in some draft standard docs, but... Comments?
- Thomas
Thomas, any status on this? If not, I should add it to the TODO list. > > Is this a TODO item? > > Sure, but I'd hate to have all of these individual items showing up as > separate things in some ToDo list, since it won't paint a coherent > picture of where things are headed. > > I'm planning on doing some work on timestamp, which will include: > > o support for "ISO variants" on input, including embedded "T" preceeding > the time fields > > o deprecation of 'current' (holdover from Original Postgres) > > o deprecation of 'invalid' for timestamp at least (holdover from > Original Postgres) > > o (probably) deprecation of "ignored fields" if the value not explicitly > recognized (holdover from Original Postgres) > > o resolution of the SQL99 vs SQL-useful timestamp and timestamp with > time zone issues > > The latter has two possible outcomes (maybe more): > > a) we keep the current timestamp implementation as either timestamp or > timestamp with time zone, and implement the other as a new type with > much common underlying code > > b) we roll back decisions made a few years ago, and have "SQL-useful > timestamp" become datetime, leaving timestamp with time zone and > timestamp with slavish SQL99 compliance as undersupported, ineffective > and near-useless data types (an overstatement for simple timestamp, but > not for timestamp with time zone). > > For those who haven't used a fully compliant timestamp with time zone > (and most haven't, since it is brain damaged) the time zone is specified > as a single offset from GMT. No provisions for DST, etc etc. > > The current identification of timestamp as "timestamp with time zone" > was to prepare for implementation of a "no time zone anywhere" timestamp > in 7.2. The current timestamp would become "timestamp with time zone", > with time zone support substantially enhanced from SQL99 specs. I'll > speak for the silent majority to claim that these enhancements are > useful. They are likely compatible enough (or could be) to pass SQL9x > compliance testing, unless that testing includes cases which try to > enforce the worst aspects of the standard. > > Hmm, now that I look at it again, SQL99 timestamp with time zone may not > be too far away from our current timestamp, except for issues concerning > UTC vs local time and probably some other details of formatting and > behavior (e.g. allowed date ranges; we allow more). > > It appears that SQL99 timestamp with time zone outputs as UTC (which is > how it is stored internally already) so the standard is missing the > notion of representing time zones in the output of a timestamp or > timestamp with time zone type. This is not as horrendous as SQL92 or as > described in some draft standard docs, but... Comments? > > - Thomas > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Thomas, any status on this? If not, I should add it to the TODO list.
Well, sure, there is *always* status ;)
I started coding a couple of days ago. So far, no showstoppers.
There are two related issues:
1) I should recode TIME WITH TIME ZONE to conform to SQL99. I had done
it originally with a "persistant time zone" since the SQL9x definition
is weaker than I imagined at the time. afaict it is still a minimally
useful data type.
2) upgrading from 7.1.x to 7.2 will likely require a schema change,
since 7.1.x TIMESTAMP should become TIMESTAMP WITH TIME ZONE, but afaik
someone took out that feature during the 7.1.x series of releases. So a
7.1.x dump will give columns labeled as TIMESTAMP but with values
containing time zones. We *might* want to accept (and ignore?) time zone
fields in TIMESTAMP values for input for 7.2, but that would still leave
folks expecting a data type which recognizes time zones needing to
adjust their schemas during the upgrade.
- Thomas