[JDBC] Timestamps without time zone

From: Achilleas Mantzios <achill(at)matrix.gatewaynet.com>
Date: Tue Jan 08 2008 - 07:12:56 EST

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
Do you need more help?
In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software:
Learn more about Pantek's Technical Support offerings
Place an order for 24/7/365 Technical Support here
Learn more about Pantek, who we are, and what we do
Chat with a Live Operator
Related postgresql.org Links
pgsql-admin
pgsql-advocacy
pgsql-announce
pgsql-bugs
pgsql-cygwin
pgsql-docs
pgsql-general
pgsql-interfaces
pgsql-jdbc
pgsql-jobs
pgsql-novice
pgsql-odbc
pgsql-performance
pgsql-php
pgsql-ports
pgsql-sql
Request more information about Pantek

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Received on Tue Jan 8 07:14:56 2008