Re: Timestamps without time zone

1st off, happy new year to everybody.
I know the issue with timestamps without time zone and the various set/get
methods maybe has been discussed exhaustively, and i understand that for new
applications serious studying must take place before any implementation
decisions are taken.

However it seems for my case that i am really trapped, and i ask your advice:
(We run 1 central master server with jboss, postgresql 7.4.18 and about 40
slave (heavily prunned clones) servers on the 7 seas over uucp satellite
connections.
Let me say in advance that the facts that a) we dont always have login
prompt/cheap comms to the servers
b) the heavy usage of our version of dbmirror + c) the size of the
database/apps +d) lack of adequate manforce, makes it quite hard to upgrade
to 8.2+.
I *shall* do it some time and i hope within 2008, though).

Now the problem.
I keep gps data timestamps as "timestamp without time zone".
(i'll use this to explain my problem, altho i have "timestamp without time
zone" which suffer from the same effects, in various other apps as well).
Our server local time zone is set to EET (Athens/Greece) at winter months
and EEST at summer months.

I had identified the problem, on some standalone applications and i used the
brute-force method of
java.util.TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
so all related problems were gone.

However if i do it on the jboss JVM then the whole thing gets screwed up, as
it will be unsafe to touch a JVM global property, just for the sake of some
operations, on behalf of some user.
One solution would be to synchronize on blocks containing Timestamp operations
like
syncronized(some global application object) {
java.util.TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
// database operations here
......
java.util.TimeZone.setDefault(null);
}

This way i can solve the problem at the expense of some loss of concurrency.
Untill now i postponed talking about the problem in action, so let me now post
an example.
I have the following table
dynacom=# \d gpscookeddata
Table "public.gpscookeddata"
Column | Type | Modifiers
-----------+-----------------------------+-----------
vslid | integer | not null
utcts | timestamp without time zone | not null
latid | double precision |
longi | double precision |
tracktrue | double precision |
avgspeed | double precision |
minspeed | double precision |
maxspeed | double precision |
Indexes:
"gpscookeddata_pkey" primary key, btree (vslid, utcts)
"gpscookeddata_utcts" btree (utcts)
"gpscookeddata_vslid" btree (vslid)

where utcts holds the UTC (GMT) timestamp.
some sample data:
select utcts,latid,longi,tracktrue,avgspeed,minspeed,maxspeed from
gpscookeddata where vslid=92 and utcts<'2006-03-26 04:00:00' and
utcts>='2006-03-26 02:00:00' order by 1;
utcts | latid | longi | tracktrue | avgspeed | minspeed |
maxspeed
---------------------+----------+----------+-----------+----------+----------+----------
2006-03-26 02:29:49 | -2256.13 | -3707.46 | 211.1 | 13.6 | 13.3 |
14
2006-03-26 02:59:49 | -2302.31 | -3703.83 | 207.7 | 14 | 13.8 |
14.1
2006-03-26 03:29:49 | -2308.7 | -3700.11 | 209.4 | 14.4 | 14 |
14.6
2006-03-26 03:59:49 | -2315.16 | -3656.16 | 210.4 | 14.8 | 14.5 |
15
(4 rows)

Now if the application wants to read data between start='2006-03-26 02:00:00'
and end='2006-03-26 04:00:00'

java.text.SimpleDateFormat dfrm =
new java.text.SimpleDateFormat ("yyyy-MM-dd HH:mm:ss");
dfrm.setTimeZone(TimeZone.getTimeZone("GMT"));
java.util.Date gstartDate = dfrm.parse(start);
java.util.Date gendDate = dfrm.parse(end);

//here the two dates have the correct values (millisecond wise)

st = con.prepareStatement("select
utcts,latid,longi,tracktrue,avgspeed,minspeed,maxspeed from gpscookeddata
where vslid=? and utcts<? and utcts>=? order by utcts");
st.setInt(1,Integer.parseInt(vslid));
st.setTimestamp(2,new java.sql.Timestamp(gendDate.getTime()));
st.setTimestamp(3,new java.sql.Timestamp(gstartDate.getTime()));

if i do that then in the pgsql.log i get
select utcts,latid,longi,tracktrue,avgspeed,minspeed,maxspeed from
gpscookeddata where vslid=92 and utcts<'2006-03-26 07:00:00.000000+03' and
utcts>='2006-03-26 05:00:00.000000+03' order by utcts
which does a wrong WHERE since the '+03' part is disragarded (correctly) by
postgresql. So while technically the '2006-03-26 07:00:00.000000+03' value is
fine, and the timestamp is indeed that one, this does not work in the query.
If i rewrite the last 2 statements (as Chip Gobs suggested recently in the
list, http://archives.postgresql.org/pgsql-jdbc/2007-12/msg00013.php) as

st.setTimestamp(2,Timestamp.valueOf(end));
st.setTimestamp(3,Timestamp.valueOf(start));

then it does
select utcts,latid,longi,tracktrue,avgspeed,minspeed,maxspeed from
gpscookeddata where vslid=92 and utcts<'2006-03-26 04:00:00.000000+03' and
utcts>='2006-03-26 02:00:00.000000+02' order by utcts
which although technically wrong gives the right query results
(notice that at 2006-03-26 03:00:00 EET the time zone is increased by 1 (+03),
to reflect dayligght savings).
Even then, it seems that jdbc does yet another conversion when transfering
timestamp values:

rs = st.executeQuery();
while (rs.next()) {
java.util.Date thists = rs.getTimestamp(1);
....
System.out.println("utc="+thists);
....
}
rs.close();
st.close();

The above gives,
utc=2006-03-26 02:29:49.0
utc=2006-03-26 02:59:49.0
utc=2006-03-26 04:29:49.0 (!!! +1)
utc=2006-03-26 04:59:49.0 (!!! +1)
so it interprets the above dates as greek dates, so at this point i have lost
track and cant get the right dates.

As you may have found (if you have read up to this point - i hope!!),
i have not found a decent way to deal with this beast.
Taking into account the huge difficulty to make everything "with time zone" in
the database, along with the fact that some "local" dates really have *no*
accompanying timezone info, and thus presenting them as "with time zone"
would be a lie, how should deal with this?

Any help much appreciated.
Sorry for the length of my post.
--
Achilleas Mantzios

Στις Tuesday 08 January 2008 14:12:56 ο/η Achilleas Mantzios έγραψε:
>...
> java.text.SimpleDateFormat dfrm =
> new java.text.SimpleDateFormat ("yyyy-MM-dd HH:mm:ss");
> dfrm.setTimeZone(TimeZone.getTimeZone("GMT"));
> java.util.Date gstartDate = dfrm.parse(start);
> java.util.Date gendDate = dfrm.parse(end);
>
> //here the two dates have the correct values (millisecond wise)
The input values passed are
start: 2006-03-26 02:00:00
end : 2006-03-26 04:00:00
It is confirmed by System.out.println("gstartDate="+gstartDate); which gives
"Sun Mar 26 05:00:00 EEST 2006" and "Sun Mar 26 07:00:00 EEST 2006"
and which is absolutely correct (EEST is +03, so at least up to this point
the millisecond values of gstartDate , gendDate are correct).
>
> st = con.prepareStatement("select
> utcts,latid,longi,tracktrue,avgspeed,minspeed,maxspeed from gpscookeddata
> where vslid=? and utcts<? and utcts>=? order by utcts");
> st.setInt(1,Integer.parseInt(vslid));
> st.setTimestamp(2,new java.sql.Timestamp(gendDate.getTime()));
> st.setTimestamp(3,new java.sql.Timestamp(gstartDate.getTime()));
Now instead of the above i tried something that should be "more" correct and
according to specs
Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
st = con.prepareStatement("select
utcts,latid,longi,tracktrue,avgspeed,minspeed,maxspeed from gpscookeddata
where vslid=? and utcts<? and utcts>=? order by utcts");
st.setInt(1,Integer.parseInt(vslid));
st.setTimestamp(2,new java.sql.Timestamp(gendDate.getTime()),cal);
st.setTimestamp(3,new java.sql.Timestamp(gstartDate.getTime()),cal);
however again i see that the JDBC insists producing code like:
select utcts,latid,longi,tracktrue,avgspeed,minspeed,maxspeed from
gpscookeddata where vslid=92 and utcts<'2006-03-26 07:00:00.000000+03' and
utcts>='2006-03-26 05:00:00.000000+03' order by utcts
postgresql backend discards the +03 (as specified by the docs) so we come to
the question:
how can it be done, without dirty tricks? and i am not yet at the point to
interpret rs.getTimestamp(1) (from the above query yet).
What is the best practice (if any) to deal with "timestamps without time
zone"?

In the java docs it says about
PreparedStatement.setTimestamp(int parameterIndex,Timestamp x, Calendar cal):
"Sets the designated parameter to the given java.sql.Timestamp value, using
the given Calendar object. The driver uses the Calendar object to construct
an SQL TIMESTAMP value, which the driver then sends to the database. With a
Calendar object, the driver can calculate the timestamp taking into account a
custom timezone. If no Calendar object is specified, the driver uses the
default timezone, which is that of the virtual machine running the
application."
If we explicitly tell the driver that we want our effective SQL TIMESTAMP
value to be with cal timezone, why the driver inserts the JVM default time
zone?

>..

--
Achilleas Mantzios

Achilleas Mantzios wrote:

> Now instead of the above i tried something that should be "more" correct and
> according to specs
> Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
> st = con.prepareStatement("select
> utcts,latid,longi,tracktrue,avgspeed,minspeed,maxspeed from gpscookeddata
> where vslid=? and utcts<? and utcts>=? order by utcts");
> st.setInt(1,Integer.parseInt(vslid));
> st.setTimestamp(2,new java.sql.Timestamp(gendDate.getTime()),cal);
> st.setTimestamp(3,new java.sql.Timestamp(gstartDate.getTime()),cal);
> however again i see that the JDBC insists producing code like:
> select utcts,latid,longi,tracktrue,avgspeed,minspeed,maxspeed from
> gpscookeddata where vslid=92 and utcts<'2006-03-26 07:00:00.000000+03' and
> utcts>='2006-03-26 05:00:00.000000+03' order by utcts

This is unexpected - as you say the driver should generate timestamp
values in UTC if you pass a UTC calendar to setTimestamp.

Which driver version are you using?
How are you concluding that the JDBC driver is sending +03 values?

The driver will not generate a query string like you quoted above as it
uses parameter placeholders (assuming you are using protocol version 3
anyway) and sends the values out of line, which is why I'm asking.

-O

Στις Wednesday 09 January 2008 14:43:25 ο/η Oliver Jowett έγραψε:
> Achilleas Mantzios wrote:
> > Now instead of the above i tried something that should be "more" correct
> > and according to specs
> > Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
> > st = con.prepareStatement("select
> > utcts,latid,longi,tracktrue,avgspeed,minspeed,maxspeed from
> > gpscookeddata where vslid=? and utcts<? and utcts>=? order by utcts");
> > st.setInt(1,Integer.parseInt(vslid));
> > st.setTimestamp(2,new java.sql.Timestamp(gendDate.getTime()),cal);
> > st.setTimestamp(3,new java.sql.Timestamp(gstartDate.getTime()),cal);
> > however again i see that the JDBC insists producing code like:
> > select utcts,latid,longi,tracktrue,avgspeed,minspeed,maxspeed from
> > gpscookeddata where vslid=92 and utcts<'2006-03-26 07:00:00.000000+03'
> > and utcts>='2006-03-26 05:00:00.000000+03' order by utcts
>
> This is unexpected - as you say the driver should generate timestamp
> values in UTC if you pass a UTC calendar to setTimestamp.
>
> Which driver version are you using?
Its the 7.4.19 built from source.
> How are you concluding that the JDBC driver is sending +03 values?
by the postgresql log, after setting log_statement = true
>
> The driver will not generate a query string like you quoted above as it
> uses parameter placeholders (assuming you are using protocol version 3
> anyway) and sends the values out of line, which is why I'm asking.
>
> -O

--
Achilleas Mantzios

Achilleas Mantzios wrote:

>> Which driver version are you using?
> Its the 7.4.19 built from source.

Ah, there is your problem then. That is a very old driver and had
problems with exactly the case you're trying to use (setTimestamp() with
a Calendar), amongst many other bugs.

Can you try a recent driver and see if it fixes your problem? Looking at
CVS history it looks like 8.1 is the earliest driver with
timezone-related fixes, but I'd suggest you try the most recent stable
driver.

Newer drivers will still talk to older servers, so you shouldn't need to
upgrade your server to test this.

-O

Στις Wednesday 09 January 2008 15:05:24 ο/η Oliver Jowett έγραψε:
> Achilleas Mantzios wrote:
> >> Which driver version are you using?
> >
> > Its the 7.4.19 built from source.
>
> Ah, there is your problem then. That is a very old driver and had
> problems with exactly the case you're trying to use (setTimestamp() with
> a Calendar), amongst many other bugs.
>
> Can you try a recent driver and see if it fixes your problem? Looking at
> CVS history it looks like 8.1 is the earliest driver with
> timezone-related fixes, but I'd suggest you try the most recent stable
> driver.
>
> Newer drivers will still talk to older servers, so you shouldn't need to
> upgrade your server to test this.

Thanx a lot,i'll try that right away.

>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--
Achilleas Mantzios

Στις Wednesday 09 January 2008 15:05:24 ο/η Oliver Jowett έγραψε:
> Achilleas Mantzios wrote:
> >> Which driver version are you using?
> >
> > Its the 7.4.19 built from source.
>
> Ah, there is your problem then. That is a very old driver and had
> problems with exactly the case you're trying to use (setTimestamp() with
> a Calendar), amongst many other bugs.
>
> Can you try a recent driver and see if it fixes your problem? Looking at
> CVS history it looks like 8.1 is the earliest driver with
> timezone-related fixes, but I'd suggest you try the most recent stable
> driver.
>
> Newer drivers will still talk to older servers, so you shouldn't need to
> upgrade your server to test this.

I tried with postgresql-8.2-507.jdbc3.jar, and it works like a charm!
Not only that, but the getTimestamp() too! I was starting to question my night
sleep quality, read old lenghty articles about java dates/timezones etc!!
Thank you a lot! I didnt know jdbc newer versions were backward compatible
with older servers.

Just a comment:
Why not merge from current changes/bugfixes to the JDBC?
I mean 7.4 is old ok, but *officially supported* also!

>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--
Achilleas Mantzios

Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> writes:
> Wednesday 09 January 2008 15:05:24 / Oliver Jowett :
>> Ah, there is your problem then. That is a very old driver and had
>> problems with exactly the case you're trying to use (setTimestamp() with
>> a Calendar), amongst many other bugs.

> Just a comment:
> Why not merge from current changes/bugfixes to the JDBC?
> I mean 7.4 is old ok, but *officially supported* also!

Back-patching this type of behavioral change would be a seriously bad
idea, because it would break applications that are expecting the old
behavior.

regards, tom lane

Στις Wednesday 09 January 2008 17:42:57 ο/η Tom Lane έγραψε:
> Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> writes:
> > Wednesday 09 January 2008 15:05:24 / Oliver Jowett :
> >> Ah, there is your problem then. That is a very old driver and had
> >> problems with exactly the case you're trying to use (setTimestamp() with
> >> a Calendar), amongst many other bugs.
> >
> > Just a comment:
> > Why not merge from current changes/bugfixes to the JDBC?
> > I mean 7.4 is old ok, but *officially supported* also!
>
> Back-patching this type of behavioral change would be a seriously bad
> idea, because it would break applications that are expecting the old
> behavior.
That is true indeed, but on the other hand a lot of people write extremely
buggy code (and some times intentionally as in my case) in order to deal
with those old JDBC drivers bugs. I mean those people would have to face
the reality sooner or later when upgrading to 8.x, why not force them to take
the action earlier, when, in addition, the new correct behaviour adheres to
the JDBC spec, whereas the old one didnt?
>
> regards, tom lane

--
Achilleas Mantzios

On Wed, 9 Jan 2008, Achilleas Mantzios wrote:

> Why not merge from current changes/bugfixes to the JDBC?
> I mean 7.4 is old ok, but *officially supported* also!
>

From the JDBC team the last version we support is 8.0 and as Oliver has
noted timestamps don't work correctly there either. Some changes are just
too big/dangerous to backpatch.

http://jdbc.postgresql.org/download.html

Kris Jurka

Achilleas Mantzios wrote:

> Why not merge from current changes/bugfixes to the JDBC?
> I mean 7.4 is old ok, but *officially supported* also!

Pre 8.0 the JDBC driver was included with the server source.

Post 8.0 it was shipped separatedly, because amongst other things the
development cycle of the JDBC driver didn't match the server development
cycle well.

For 7.4 the decision about what to declare as "supported" is somewhat
out of our hands because the JDBC driver is only a small part of the
large system.

We really only support driver versions 8.0 and later - 7.4 gets security
and trivial bugfixes and that's about it. There were large architectural
changes in the 8.0 driver and so it's not a simple task to backport most
bugfixes to the 7.4 code. And given that newer drivers still talk to
older servers (back to 7.2) it's not really a pressing concern since
upgrading the driver works for most apps.

-O

Στις Wednesday 09 January 2008 22:40:11 ο/η Kris Jurka έγραψε:
> On Wed, 9 Jan 2008, Achilleas Mantzios wrote:
> > Why not merge from current changes/bugfixes to the JDBC?
> > I mean 7.4 is old ok, but *officially supported* also!
> >
> >
> From the JDBC team the last version we support is 8.0 and as Oliver has
>
> noted timestamps don't work correctly there either. Some changes are just
> too big/dangerous to backpatch.
>
> http://jdbc.postgresql.org/download.html

I tried other apps with the new postgresql-8.2-507.jdbc3.jar and some of them
break. So i will need to live with the 7.4.19 driver for a while.
One question, is there a way to get a hold of the Default TimeZone instance
of the JVM (lets call it default_TZ) and put all code that alter it in some
sort of
synchronized (default_TZ) {
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
// jdbc calls here that deal with timestamps without time zone
java.util.TimeZone.setDefault(null);
}
block?
Any date/time/Calendar operation in java effectively tries to access the
default TimeZone.
So, the above would make the system work perfectly from a correctness point of
view (losing in parallelism of course) as a workaround until we upgrade
to 8.x when ofcourse i will have to rewrite all portions of code (among
others) that deal with timestamps without time zone.
What do you think?
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Achilleas Mantzios

On Thu, 10 Jan 2008, Achilleas Mantzios wrote:

> I tried other apps with the new postgresql-8.2-507.jdbc3.jar and some of
> them break. So i will need to live with the 7.4.19 driver for a while.

Without an actual failure it's tough to advise you here. You might try
the URL option protocolVersion=2 or stringtype=unspecified to solve some
of the more common problems people have come across with the upgrade as a
workaround.

> One question, is there a way to get a hold of the Default TimeZone
> instance of the JVM (lets call it default_TZ) and put all code that
> alter it in some sort of
> synchronized (default_TZ) {
> TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
> // jdbc calls here that deal with timestamps without time zone
> java.util.TimeZone.setDefault(null);
> }
> block?

This seems unlikely to work. Everyone would have to get the default_TZ
object before doing any TZ manipulation so they'd synchronzie on the same
object. And there's no way you can control other code that just wants
to print out a date. It's not going to synchronize itself on anything.

Kris Jurka

Στις Friday 11 January 2008 07:12:24 ο/η Kris Jurka έγραψε:
>
> On Thu, 10 Jan 2008, Achilleas Mantzios wrote:
>
> > I tried other apps with the new postgresql-8.2-507.jdbc3.jar and some of
> > them break. So i will need to live with the 7.4.19 driver for a while.
>
> Without an actual failure it's tough to advise you here. You might try
> the URL option protocolVersion=2 or stringtype=unspecified to solve some
> of the more common problems people have come across with the upgrade as a
> workaround.
>
for example the following exception:
org.postgresql.util.PSQLException: Operation requires a scrollable ResultSet, but this ResultSet is FORWARD_ONLY
i have the ResultSet.last() call at least 290 times in my code so i should
adjust the relevant con.prepareStatement() to make the ResultSet scrollable.
I cannot do that at the moment.
I didnt post this actual problem, cause apart from the fact that it is natural
to happen, there could be others that i just dont know of yet. So due to lack
of time i didnt write about it at all.
> > One question, is there a way to get a hold of the Default TimeZone
> > instance of the JVM (lets call it default_TZ) and put all code that
> > alter it in some sort of
> > synchronized (default_TZ) {
> > TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
> > // jdbc calls here that deal with timestamps without time zone
> > java.util.TimeZone.setDefault(null);
> > }
> > block?
>
> This seems unlikely to work. Everyone would have to get the default_TZ
> object before doing any TZ manipulation so they'd synchronzie on the same
> object. And there's no way you can control other code that just wants
> to print out a date. It's not going to synchronize itself on anything.
In java 1.4.2 (yes i know we are way old on this one also!), class TimeZone has:
private static TimeZone defaultZone = null;
Date in java 1.4.2 has a Calendar private field, and Calendar has a TimeZone.
All Date/Calendar manipulation eventually would access static field java.util.TimeZone.defaultZone.
If defaultZone was public, synchronizing on it would solve the problem,
unfortunately it is private.
Doing it with a "userland" object, as you say, would be impractical/impossible.
>
> Kris Jurka
>

--
Achilleas Mantzios