Re: Timestamps without time zone - Mailing list pgsql-jdbc

Στις 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