Skip site navigation (1) Skip section navigation (2)

Timestamps without time zone

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Timestamps without time zone
Date: 2008-01-08 12:12:56
Message-ID: 200801081412.57309.achill@matrix.gatewaynet.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-jdbc by date

Next:From: Gregory StarkDate: 2008-01-08 12:16:26
Subject: Re: TypeInfoCache
Previous:From: Daniel MigowskiDate: 2008-01-08 10:30:03
Subject: Re: TypeInfoCache

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group