Timestamps without time zone - Mailing list pgsql-jdbc

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