Обсуждение: TIMESTAMP WITHOUT TIME ZONE

I am stuck, I am getting two different times from the database depending on the timezone of the system I am querying from.

The story is this:
I have a table name request. It has a column create_dt of type TIMESTAMP WITHOUT TIME ZONE.

When I query this from jdbc into a java.sql.Timestamp and out put it like this

  java.sql.Timestamp ts= rs.getTimestamp(1);
  System.out.println(ts.getTime());

I get different result if I query it from my workstation(US/Easter timezone) and from the server (GMT timezone).

How can this be?? Please help!

A data type of timestamp without time zone should not do any conversions. The java.sql.Timestamp does not store any timezone info, just nano seconds from a date. Some where there is a timezone conversion happening. Why and how do I prevent it?

My idea is this:
What I save to the database (date & time) should be what I get back no matter what timezone I save or retrieve it in.

Randy

"Randy Shelley" <randy.shelley@gmail.com> writes:
> The java.sql.Timestamp does not store any timezone info, just nano seconds
> from a date.

One would hope that it's implicitly referenced to GMT, though, not some
free-floating value that means who-knows-what.

I think your fundamental error is in using timestamp without time zone
in the database.  Try with-time-zone if you want consistent results
across clients in different zones.

            regards, tom lane

Randy Shelley wrote:
> I get different result if I query it from my workstation(US/Easter
> timezone) and from the server (GMT timezone).

> A data type of timestamp without time zone should not do any
> conversions. The java.sql.Timestamp does not store any timezone info,
> just nano seconds from a date. Some where there is a timezone conversion
> happening. Why and how do I prevent it?

Tom's stated the problem, but to expand a little.

Your java.sql.Timestamp is an absolute point in time (presumably
measured from midnight 1970-01-01 GMT). Note that without the GMT there,
it would not be an absolute point in time since midnight in London was
different from midnight in New York.

The "timestamp without time zone" is NOT an absolute point in time, it
is only meaningful for a single time zone.

The "timestamp with time zone" IS an absolute time, but it DOES NOT
record the timezone you enter. Rather, it is equivalent to your
java.sql.Timestamp. If you have a client in London and another in New
York, both will display the same absolute time but in their local time
zone. So, I might see 14:00+00 whereas a New-Yorker might see 08:00+05
(if that's the right time-zone). You can ask for a specific time-zone
too (with AT TIME ZONE '...').

I think the biggest problem is that "with time zone" sounds like it's
storing a fixed time-zone when you insert a value.

--
   Richard Huxton
   Archonet Ltd

Hi Tom, Randy, et al,

I'm not fully caught up with my Readings In Postgres, but this post caught
my eye and raised a concern...

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Randy Shelley" <randy.shelley@gmail.com> writes:
> > The java.sql.Timestamp does not store any timezone info, just nano seconds
> > from a date.
>
> One would hope that it's implicitly referenced to GMT, though, not some
> free-floating value that means who-knows-what.
>
> I think your fundamental error is in using timestamp without time zone
> in the database.  Try with-time-zone if you want consistent results
> across clients in different zones.
>

I sure hope there's no issue with using timestamp without timezone
_anywhere_ in the PG world because, quite frankly, "timezone" just doesn't
cut it.

There are so many issues that I don't think I have time to justice
to them here in a short email, but, just so we've all got some idea:
First, you need at least minute, if not second offset from GMT to have
anything like a comprehensive shot at "timezone." Hour-based time zones
are simply insufficient. There are lots of places in the world with
non-hour offsets from GMT.

The second biggest issue is probably the plethora of "daylight savings
time" schemes - and they change over time: notably within the last year, a
U.S. community muffed handling such a chnage with their Canadian
neighbors. And there are the timings of changes, too - do automated
daemons know when the time changes? It's quite different in various parts
of the world. Do you blindly follow your system clock? LOTS of questions
here that are none of PG's business, but are vital to a production system
always getting it right.

Third, any presumption about when which version of a time should be valid
is bound to cause major errors at some point or another. One can't just
always hand the user a timestamp in local time on client ends because you
don't know what kind of local processing they might wish to do outside of
the database engine, not the least of which is the type of question, "was
it after their business hours?" - a local-to-local question! Therefore, as
a minimum, you _must_ provide transform functions, one to the other, and
let the caller ask for what they wanted. This is particularly tricky when
it comes to database join statements - did you give the query the GMT
version, or local version?! -oy- The headaches this can cause, even among
experts.

We at Science Tools use "timestamp without timezone" as the basis of our
handling our customer's data correctly. It's configurable, but by default
all data going into a database is converted to GMT by our software,
outside the database engine, unless explicitly directed otherwise. To
handle the optionality of this, all join operations happen using what we
call "database time", so if a db doesn't store in GMT for some reason, we
still know what to do (for example, converting to the equivalent local
time of the server). We track client's GMT offsets - stored in the db, of
course - so we've got every client's offset data when needed, etc, etc,
etc.

...I PRESUME there's nothing broken about "timestamp without timezone"
within either the engine or the JDBC drivers, but I'd also caution to
always punt on the question of whether or not someone should or shouldn't
use Postgres' time zone feature. Perhaps a "for most people" qualifier,
or, "when every user is in an hour-offset from GMT timezone", etc., but
even then, multi-time-zone applications need to be VERY warry.

Respectfully,
Richard

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

Richard Troy <rtroy@ScienceTools.com> writes:
> I'm not fully caught up with my Readings In Postgres, but this post caught
> my eye and raised a concern...

AFAIK, all the reasons you enumerate are good reasons to delegate the
problem to a timestamp WITH time zone column.

> First, you need at least minute, if not second offset from GMT to have
> anything like a comprehensive shot at "timezone."

Got that.

> The second biggest issue is probably the plethora of "daylight savings
> time" schemes - and they change over time:

Got that, if you keep your zic data files up to date.

> Third, any presumption about when which version of a time should be valid
> is bound to cause major errors at some point or another.

No doubt, but doing conversions outside the database is surely no safer
than doing them inside.

> We at Science Tools use "timestamp without timezone" as the basis of our
> handling our customer's data correctly. It's configurable, but by default
> all data going into a database is converted to GMT by our software,
> outside the database engine, unless explicitly directed otherwise.

I think you're just reinventing timestamp with time zone.  Maybe if you
need to work with other DBs besides Postgres, you'll have to program to
the lowest common denominator, but PG gets all these things right.

            regards, tom lane

On Wed, 13 Dec 2006, Richard Huxton wrote:
> Randy Shelley wrote:
> > I get different result if I query it from my workstation(US/Easter
> > timezone) and from the server (GMT timezone).
>
> > A data type of timestamp without time zone should not do any
> > conversions. The java.sql.Timestamp does not store any timezone info,
> > just nano seconds from a date. Some where there is a timezone conversion
> > happening. Why and how do I prevent it?
>
> Tom's stated the problem, but to expand a little.
>
> Your java.sql.Timestamp is an absolute point in time (presumably
> measured from midnight 1970-01-01 GMT). Note that without the GMT there,
> it would not be an absolute point in time since midnight in London was
> different from midnight in New York.
>
> The "timestamp without time zone" is NOT an absolute point in time, it
> is only meaningful for a single time zone.

WRONG. It's a point in time that's meaningful to ME even if YOU can't tell
where in the universe it's supposed to represent relative to any other
point.

>
> The "timestamp with time zone" IS an absolute time, but it DOES NOT
> record the timezone you enter. Rather, it is equivalent to your
> java.sql.Timestamp. If you have a client in London and another in New

OHMYGODYOUJUSTHAVETOBEWRONG!!!

Let me get this straight; You're saying that you SUPPORT the idea of
conversion being performed by the database (or it's cohort, the JDBC
library in this case) when I, the author of an application using the data,
depend on my database to give me back the data I gave it?!

HORRIBLY BROKEN IF SO.


> York, both will display the same absolute time but in their local time
> zone. So, I might see 14:00+00 whereas a New-Yorker might see 08:00+05
> (if that's the right time-zone). You can ask for a specific time-zone
> too (with AT TIME ZONE '...').
>
> I think the biggest problem is that "with time zone" sounds like it's
> storing a fixed time-zone when you insert a value.

...I missed the start of this thread but the CORRECT behavior for Postgres
regarding TIMESTAMP WITHOUT TIMEZONE is to take a timestamp in whatever
form _I_ care to give it and return it _exactly,_ unmodified in any way.

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.

PLEASE tell me that when I give a PostgreSql server, through JDBC, a
timestamp, stored in a TIMESTAMP WITHOUT TIMEZONE attribute, that it'll
always give me back the _same_exact_bits_ as what I gave it! Anything else
is horribly broken and is, to quote Tom Lane, "about as good a definition
of corrupted data as I can think of." - with appologies to Tom, of course.

Regards,
Richard

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

On Fri, Dec 15, 2006 at 12:10:24PM -0800, Richard Troy wrote:
> > I think your fundamental error is in using timestamp without time zone
> > in the database.  Try with-time-zone if you want consistent results
> > across clients in different zones.
>
> I sure hope there's no issue with using timestamp without timezone
> _anywhere_ in the PG world because, quite frankly, "timezone" just doesn't
> cut it.
>
> There are so many issues that I don't think I have time to justice
> to them here in a short email, but, just so we've all got some idea:
> First, you need at least minute, if not second offset from GMT to have
> anything like a comprehensive shot at "timezone." Hour-based time zones
> are simply insufficient. There are lots of places in the world with
> non-hour offsets from GMT.

I don't think you understand the meaning of "timestamp with timezone".
No timezone is stored. What is stored it number of seconds since epoch
and that is rotated to the client's timezone on display.

> The second biggest issue is probably the plethora of "daylight savings
> time" schemes - and they change over time: notably within the last year, a
> U.S. community muffed handling such a chnage with their Canadian
> neighbors. And there are the timings of changes, too - do automated
> daemons know when the time changes? It's quite different in various parts
> of the world. Do you blindly follow your system clock? LOTS of questions
> here that are none of PG's business, but are vital to a production system
> always getting it right.

Quite, which is why the timezone is not stored, too ambiguous.
Automated daemons only use seconds since epoch. hour/minutes/seconds are
for people, not computers.

> Third, any presumption about when which version of a time should be valid
> is bound to cause major errors at some point or another. One can't just
> always hand the user a timestamp in local time on client ends because you
> don't know what kind of local processing they might wish to do outside of
> the database engine, not the least of which is the type of question, "was
> it after their business hours?" - a local-to-local question! Therefore, as
> a minimum, you _must_ provide transform functions, one to the other, and
> let the caller ask for what they wanted. This is particularly tricky when
> it comes to database join statements - did you give the query the GMT
> version, or local version?! -oy- The headaches this can cause, even among
> experts.

There are transform functions, the AT TIME ZONE 'blah' construct will
convert between "timestamp with timezone" and "timestamp without
timezone".

For join statements, postgres doesn't let you compare the with and
without timezone variants. You have to specify the timezone you wish to
compare in, or the comparison has no meaning.

> We at Science Tools use "timestamp without timezone" as the basis of our
> handling our customer's data correctly. It's configurable, but by default
> all data going into a database is converted to GMT by our software,
> outside the database engine, unless explicitly directed otherwise. To
> handle the optionality of this, all join operations happen using what we
> call "database time", so if a db doesn't store in GMT for some reason, we
> still know what to do (for example, converting to the equivalent local
> time of the server). We track client's GMT offsets - stored in the db, of
> course - so we've got every client's offset data when needed, etc, etc,
> etc.

You can do it that way. Alternatively, you can give the timezone to
postgres and let it do the conversion to GMT. That at least has the
advantage that you know all clients are using the same timezone
definitions.

Internally, postgres only uses GMT.

> ...I PRESUME there's nothing broken about "timestamp without timezone"
> within either the engine or the JDBC drivers, but I'd also caution to
> always punt on the question of whether or not someone should or shouldn't
> use Postgres' time zone feature. Perhaps a "for most people" qualifier,
> or, "when every user is in an hour-offset from GMT timezone", etc., but
> even then, multi-time-zone applications need to be VERY warry.

It looks like you understand the issues, so if you're using it
correctly, fine. Most people do not, and many try to use "timestamp
without timezone" to store local times, which does not work.

The good rule of thumb is basically:
- timestamp with timezone is for recording an instant in time (seconds
since epoch).
- timestamp without timezone is for recording what appears on a clock
face, that will be constant wherever the client is.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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

> 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/

Richard Troy wrote:

> 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."

If you pass a timestamp without time zone, the given timestamp will be
given back to you on request, no changes applied, whatever the timezone
either the inserter or the extracter are on.

If you pass a timestamp with time zone, the time will be rotated to UTC
on insert depending on the inserter's timezone (thus it's stored as
UTC), and will be rotated "back" to the extracter's timezone when you
extract it.  Note that both timezones may be different, so the numbers
you get may be different than the numbers you put in, but they will
signal exactly the same instant in time (in the appropriate time zone).

If you want to know what time zone the inserter used, you would store
that in a separate column.

Richard Troy <rtroy@ScienceTools.com> writes:
> That said, nobody has yet assured me that when I give a timestamp I get it
> back unmolested.

Well, as far as the backend is concerned you do get it back unmolested
(up to the limits of float roundoff error, if you use float timestamps):
  * unconditionally for timestamp without timezone;
  * if your TimeZone is the same as it was on insert, for timestamp
    with timezone.  (If you change the timezone setting you'll get a
    suitably rotated value, and no that's not a bug.)  Also you have
    to be careful not to pass in a nonexistent or ambiguous value during
    DST changes, else you might get an adjusted value back.

If you always run with TimeZone = GMT then there's effectively no
difference between timestamp with and without time zone.

Now the original context of this thread was what happens with the JDBC
driver, and that I'm not sure about --- they have a problem because they
have to map both types to the same Java type, and it doesn't fit real
well.  But you'd be better off asking on pgsql-jdbc if you want the gory
details about that.

            regards, tom lane

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