Re: Re: Timestamp without time zone

Vivek Khera <khera(at)kcilink(dot)com> writes:
> "PS" == Pasi Salminen <pasi(dot)salminen(at)khf(dot)fi> writes:
> PS> I am trying to create table with a field which type is timestamp
> PS> and I don't want it to be timestamp with time zone.

> This question has been asked at least 4 times since I've been
> following this list, including once by me.

> Appaarently, nobody seems to have an answer. The docs imply that type
> timstamp is without timezone, since there is a type "timestamp with
> timezone".

In 7.1, "timestamp with timezone" is the official name of type
"timestamp". The datatype is the same as it's always been: it is a
timestamp stored internally as UTC (GMT), with *no* local timezone
information. On display, a timestamp value is translated to whatever
timezone is currently specified by SET TIMEZONE, and that zone is also
assumed during input conversion to UTC if the input string doesn't give
a specific timezone.

This behavior doesn't exactly correspond to either of the SQL92-defined
timestamp types (with or without timezone), but Thomas Lockhart insisted
that "with timezone" was a nearer approximation than "without".
I didn't and still don't agree with that ... maybe Thomas will pop up
on this thread and explain the reasoning, because I can't follow it.

Both of the two SQL92-defined behaviors are sufficiently brain dead that
no one here is in a hurry to replicate them exactly, so it's only a
question of what we choose to call the type we have. As of 7.1, asking
for "timestamp with time zone" or "timestamp without time zone" will
both give you the exact same "timestamp" datatype. Personally I think
that's fine, but it should display as plain "timestamp" and lose the
confusing, pointless extra verbiage.

If you want more detail, you can find the arguments about this in the
pghackers mail archives, sometime last year IIRC.

regards, tom lane