Re: TIMESTAMP WITH( OUT)? TIME ZONE indexing/type choice...

I think a switch would be useful, because it means that people who don't
need the TZ can be compliant if they want to. The default should be WITH
time zone. When the Time Zone is off, the time zone is actually recorded as
+00, so

The NO timezone version makes sense for people who only want to time stamp
events in their computer. For those people, the TZ is wasted space. Maybe
they wrote the spec. The TZ is only useful if you want to record events in
different time zones - ie - outside your computer. The argument for the
simpler, no time zone version would be that you can convert any time to any
time zone, in the special case (to them, more general case in my opinion)
where you have info from multiple time zones.

I am archiving mail messages using a timestamptz. The time zone is useful to
me. Of course, I usually program in OmniMark, which uses a 4 digit time zone
with date/time values, so it is easy and natural to record times that way.

Yes, I said 4 digit time zone. Have you heard of Newfoundland? Their time
zone is 1/2 hour off the grid, I think it's minus 3hrs and 30 minutes. You
can't represent that in PostgreSQL, but you can in OmniMark. There are
several other areas with time zones that are not exact hours, possibly not
half hours.

Robert Echlin
Infrastructure Specialist
OmniMark Technologies
Ottawa, Ontario, Canada
rechlin@omnimark.com

This message, including any attachments, is for the sole use of the
intended recipient and may contain confidential and privileged
information. Any unauthorized review, use, disclosure, copying, or
distribution is strictly prohibited. If you are not the intended
recipient please contact the sender by reply email and destroy
all copies of the original message and any attachments.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Greg Stark" <gsstark@mit.edu>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, February 19, 2003 1:34 AM
Subject: Re: [GENERAL] TIMESTAMP WITH( OUT)? TIME ZONE indexing/type
choice...


> Greg Stark <gsstark@mit.edu> writes:
> > I just don't see what use a timestamp that doesn't represent a
particular time
> > would ever be. It seems to serve only as a gotcha for unwary programmers
who
> > take the default.
>
> I have a problem with that too :-( ... timestamptz seems much the more
> generally useful type.  But the SQL spec is pretty definite that
> "timestamp" means "timestamp without time zone".
>
> Is anybody interested in the idea of a GUC parameter to choose whether
> "timestamp" means "timestamp with time zone" (useful) or "timestamp
> without time zone" (anally spec-compliant)?  Or would that just muddy
> the waters even more than they are already?  We already changed the
> meaning once...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html