timestamp with/without time zone

Let's switch 'timestamp with time zone' back to 'timestamp'. This just
makes no sense.

Imho it only makes no sense, since the impl does not conform to standard :-(
The "with time zone" requests, that the client timezone be stored in the row.
The "timestamp" wants no timezone arithmetic/input or output at all.

Andreas

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, Pennsylvania 19026

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 time
timestamp 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, Pennsylvania 19026

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:

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, Pennsylvania 19026

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, Pennsylvania 19026

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, Pennsylvania 19026

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, Pennsylvania 19026

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

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

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 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, Pennsylvania 19026

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, Pennsylvania 19026