timestamp in pg_dump

Thomas Lockhart writes:

I'd like to have pg_dump for 7.1 produce "timestamp with time zone" when
dealing with timestamp type(s). That will prepare us for introducing a
timestamp type without time zones, while allowing reasonable upgrades to
7.2.

Comments? Would a pg_dump guru be willing to take this on? Should be
"easy", since afaik we already map some internal types to external
SQL-standard names...

Did that.

But given that 'timestamp' is really 'timestamp with time zone' and there
is no 'timestamp without time zone', isn't there something wrong with the
documentation? See User's Guide, table 3-6:

http://www.postgresql.org/devel-corner/docs/postgres/datatype-datetime.htm

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

I'd like to have pg_dump for 7.1 produce "timestamp with time zone" when
dealing with timestamp type(s). That will prepare us for introducing a
timestamp type without time zones, while allowing reasonable upgrades to
7.2.

Did that.

Great!

But given that 'timestamp' is really 'timestamp with time zone' and there
is no 'timestamp without time zone', isn't there something wrong with the
documentation? See User's Guide, table 3-6:

Hmm. Not sure *where* some of that info came from; perhaps it is a
holdover from when timestamp was implemented as a 4 byte integer. Or it
was space aliens ;) Will update asap. Thanks for catching it...

- Thomas

I'd like to have pg_dump for 7.1 produce "timestamp with time zone" when
dealing with timestamp type(s). That will prepare us for introducing a
timestamp type without time zones, while allowing reasonable upgrades to
7.2.

But the current timestamp does not store a timezone. timestamp with time zone
is supposed to store and output the timezone that was inserted.
The current timestamp has it messed up (sorry), since it does not store a timezone.
It stores time in UTC and always converts output to the timezone derived from [PG]TZ.

IMHO timestamp is currently closest to the ANSI timestamp without time zone.
Especially if you always omit a timezone for input and ignore the timezone that is output.

A reasonably easy upgrade is imho not possible :-(

Andreas

But the current timestamp does not store a timezone. timestamp with time zone
is supposed to store and output the timezone that was inserted.
The current timestamp has it messed up (sorry), since it does not store a timezone.
It stores time in UTC and always converts output to the timezone derived from [PG]TZ.

Good point, but I'll disagree with the implied conclusion. imho the
SQL9x provisions for time zone handling are fundamentally and tragically
broken, with absolutely no provisions for DST, time zone shifting, etc
etc. Which helps lead most folks to code other databases without time
zones at all.

Date and Darwen (1997) have essentially the same opinion (though the
last sentence is my own speculation).

IMHO timestamp is currently closest to the ANSI timestamp without time zone.
Especially if you always omit a timezone for input and ignore the timezone that is output.

Hmm. My thought was to implement a timestamp type without *any* time
zone manipulation, leaving our current type as the "zone-full" one, but
it should be discussed whether we need a high-fidelity implementation of
the fundamentally useless SQL9x version. fwiw, I did implement "time
with time zone", which was easy, mostly to "check the feature box" and
I'll guess that no one bothers to use it.

Comments?

- Thomas

But the current timestamp does not store a timezone. timestamp with time zone
is supposed to store and output the timezone that was inserted.
The current timestamp has it messed up (sorry), since it does not store a timezone.
It stores time in UTC and always converts output to the timezone derived from [PG]TZ.

Good point, but I'll disagree with the implied conclusion. imho the
SQL9x provisions for time zone handling are fundamentally and tragically
broken, with absolutely no provisions for DST, time zone shifting, etc
etc. Which helps lead most folks to code other databases without time
zones at all.

Ok, so you would be free to create a differently named type that does whatever
you like, but imho an ANSI standard type should behave exactly as defined.
Especially if it has such a verbose name as "timestamp with time zone".

My experience is, that developers don't use database supplied time types,
because they all behave differently, not because they are not useful.
I think we should not create yet another behavior.

Andreas