Re: TIMESTAMP WITHOUT TIME ZONE - Mailing list pgsql-general

You asked:
"I'd appreciate a clean yes/no;  From a Java application, throught PG in
both directions, the same timestamp comes back that was handed to the
JDBC
driver so long as it's stored in a "timestamp without time zone"
attribute, nomatter neither where on earth the insert/update originates,
nor where the select originates?"

No.  It returns the number of seconds since epoch, which is 1970-01-01
00:00 GMT.  If you insert '2006-12-15 20:00 EST', it basically inserts
the result of EXTRACT('epoch' FROM '2006-12-15 20:00 EST'), which is
1166230800.  It is a normal Universal Time Coordinate (UTC).

TIMESTAMP WITH TIME ZONE will precisely identify any point in time.  It
does not store the time zone information from the client.  When you the
later select the field, it returns a properly formatted string with the
time zone the server is configured for in postgresql.conf.  You can also
use AT TIME ZONE to specify a different zone if you wish.

If you want to store the time zone information the client used when it
stored the time (which is generally useless data) I suggest a varchar
field that stores the ISO acronymn for the relevant time zone or a
numeric field that stores the time adjustment in hours.

Examples (this server is EST, or GMT -05):

postgres=# SELECT '2006-12-15 20:00 PST'::TIMESTAMP WITH TIME ZONE;
      timestamptz
------------------------
 2006-12-15 23:00:00-05
(1 row)

postgres=# select '2006-12-15 20:00 PST'::TIMESTAMP WITH TIME ZONE AT
TIME ZONE
'GMT';
      timezone
---------------------
 2006-12-16 04:00:00
(1 row)



--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard Troy
Sent: Friday, December 15, 2006 5:18 PM
To: Tom Lane
Cc: Richard Huxton; Randy Shelley; pgsql-general@postgresql.org
Subject: Re: [GENERAL] TIMESTAMP WITHOUT TIME ZONE


> Richard Troy <rtroy@ScienceTools.com> writes:
> > See my post from a few minutes ago, but simply put, time/date is at
least
> > as challenging as money or multibyte character. And, simply put, the
> > Postgres implementation of timezone is INSUFFICIENT.
>
> Really?  We do all the things you have listed, and more.  AFAICS what
> you have described is an outside-the-database reinvention of PG's
> semantics for timestamp with time zone.
>
>             regards, tom lane

Hi Tom,

thanks for the prompt reply... Not much time - just a few moments to
reply
and then I have to get on with my customer's deliverables... ...ISTM I
took the meaning "TIMESTAMP WITH TIMEZONE" literally, while in reality
the
PG team has implemented the concept but "without timezone" in the
database
as a part of user data. I confess I never double checked the
implementation details thereof as it sounds obvious you're including
time
zone data in the data stored by the server. Also, of the two RDBMSes in
which I personally know the internal implementations of date/time, and
of
the ones I've talked with the engineers about, none of them get it right
or even begin to get it right, so it never occured to me that Postgres
would do so much better. Sounds like the PG team has once again thought
about the problem from a different perspective and came up with a better
answer.

That said, nobody has yet assured me that when I give a timestamp I get
it
back unmolested. As you correctly recall, yes, Science Tools supports
five
RDBMSes and need to do so as cleanly and consistently as we can, and
yes,
it's pretty hard to do all the testing, given all the permutations. And,
we're in the process of certifying both Ingres (which will make it, I'm
sure) and ANTS (which might not). So, seven RDBMS choices... -shrug-

I'd appreciate a clean yes/no;  From a Java application, throught PG in
both directions, the same timestamp comes back that was handed to the
JDBC
driver so long as it's stored in a "timestamp without time zone"
attribute, nomatter neither where on earth the insert/update originates,
nor where the select originates? Same bits, yes? Otherwise, "Houston,
we've got a problem."

Thanks again,
Richard

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy@ScienceTools.com, http://ScienceTools.com/


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq