Re: [JDBC] Timestamps without time zone

From: Achilleas Mantzios <achill(at)matrix.gatewaynet.com>
Date: Wed Jan 09 2008 - 07:30:51 EST

Óôéò 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=? 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

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

Received on Wed Jan 9 07:33:16 2008