problem with dates when using a java calendar object with a non-default timezone - Mailing list pgsql-jdbc

Hi,
    I am experiencing the following problem with postgresql: the date
value I insert into the database is different from the value I select
from the database. I use a Calendar with a non-default timezone to set
and get the date value (using methods
PreparedStatement.setDate(int,Date,Calendar) and
ResultSet.getDate(int,Calendar)).
    Do you know how can I fix this problem? Am I doing anything wrong?
    Here is my system configuration:
     Server:
        OS: Red Hat Linux 7.3
        Database: PostgreSQL 7.2
      Client:
          OS: Windows XP SP2
          Java: JDK 1.4.2_05
          PostgreSQL JDBC:  pg72jdbc2.jar
<http://jdbc.postgresql.org/download/pg72jdbc2.jar> and
pg74.215.jdbc3.jar
<http://jdbc.postgresql.org/download/pg74.215.jdbc3.jar> (the problem
happened in both versions)

    I wrote a small Java program that shows the problem. This program is
attached in this email.
    Above is the program output when executed in my client system. As
you can see, "date (0) inserted" is different from "date (0) loaded",
"date (2) inserted" is different from "date (2) loaded". This is exactly
the problem. I don't care about the time (hour, minute and second)
difference as this is a SQL DATE type. The problem is that the days in
these dates are different.

    //program output start
        date (0) inserted: 13/10/04 00:00
        date (1) inserted: 13/10/04 23:59
        date (2) inserted: 13/10/04 08:00
        date (3) inserted: 13/10/04 19:00
        date (0) loaded: 12/10/04 17:00
        date (1) loaded: 13/10/04 17:00
        date (2) loaded: 12/10/04 17:00
        date (3) loaded: 13/10/04 17:00
        **********************************
        =========
        hour: 0
        minute: 0
        second: 0
        millisecond: 0
        =========
        hour: 17
        minute: 0
        second: 0
        millisecond: 0
        =========
        year1: 2004; month1: 9; date1: 13
        year2: 2004; month2: 9; date2: 12
        dates equals? false
        **********************************
        =========
        hour: 23
        minute: 59
        second: 59
        millisecond: 999
        =========
        hour: 17
        minute: 0
        second: 0
        millisecond: 0
        =========
        year1: 2004; month1: 9; date1: 13
        year2: 2004; month2: 9; date2: 13
        dates equals? true
        **********************************
        =========
        hour: 8
        minute: 0
        second: 0
        millisecond: 0
        =========
        hour: 17
        minute: 0
        second: 0
        millisecond: 0
        =========
        year1: 2004; month1: 9; date1: 13
        year2: 2004; month2: 9; date2: 12
        dates equals? false
        **********************************
        =========
        hour: 19
        minute: 0
        second: 0
        millisecond: 0
        =========
        hour: 17
        minute: 0
        second: 0
        millisecond: 0
        =========
        year1: 2004; month1: 9; date1: 13
        year2: 2004; month2: 9; date2: 13
        dates equals? true
    //program output end

    Any help would be much appreciated.

Thanks,
    Jair Jr
import java.sql.*;

import java.util.*;
import java.text.*;

public class JDBCTest2 {

    public static void execute(Connection c,List dates,GregorianCalendar cal,boolean useGregorianCalendarInJDBC)throws
SQLException{
        DateFormat df=new SimpleDateFormat();
        df.setCalendar(cal);
        PreparedStatement ps=c.prepareStatement("insert into date_test values (?)");
        int j=0;
        for(Iterator it=dates.iterator();it.hasNext();){
            java.sql.Date d=(java.sql.Date)it.next();
            if(useGregorianCalendarInJDBC){
                ps.setDate(1,d,cal);
            }
            else{
                ps.setDate(1,d);
            }
            ps.executeUpdate();
            System.out.println("date ("+(j++)+") inserted: "+df.format(d));
        }
        ps.close();

        Statement s2=c.createStatement();
        ResultSet rs=s2.executeQuery("select * from date_test ");
        List loadedDates=new ArrayList();
        j=0;
        while(rs.next()){
            java.sql.Date d;
            if(useGregorianCalendarInJDBC){
                d=rs.getDate(1,cal);
            }
            else{
                d=rs.getDate(1);
            }
            loadedDates.add(d);
            System.out.println("date ("+(j++)+") loaded: "+df.format(d));
        }
        for(int i=0;i<dates.size();i++){
            System.out.println("**********************************");
            System.out.println("dates equal?
"+(datesEqual(cal,(java.util.Date)dates.get(i),(java.util.Date)loadedDates.get(i))));
        }
        s2.close();
    }

    public static void execute()throws SQLException,ClassNotFoundException{
        Class.forName("org.postgresql.Driver");
        Connection c=DriverManager.getConnection("jdbc:postgresql://kenny:5432/locanet_jair","postgres","");
        c.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        c.setAutoCommit(true);

        TimeZone tz= new SimpleTimeZone(-10 * 60 * 60 * 1000,"BLABLA");
        GregorianCalendar gc=new GregorianCalendar();
        gc.setTimeZone(tz);
        List dates=new ArrayList();
        dates.add(setTimeInDate(gc,new java.sql.Date(System.currentTimeMillis()),0,0,0,0));
        dates.add(setTimeInDate(gc,new java.sql.Date(System.currentTimeMillis()),23,59,59,999));
        dates.add(setTimeInDate(gc,new java.sql.Date(System.currentTimeMillis()),8,0,0,0));
        dates.add(setTimeInDate(gc,new java.sql.Date(System.currentTimeMillis()),19,0,0,0));

        Statement s=c.createStatement();
        try{
            s.execute("drop table date_test");
        }
        catch(SQLException e){
            System.err.println("SQLException while trying to drop 'date_test' table. 'date_test' table probably does
notexist. Ignoring exception."); 
            e.printStackTrace();
        }
        s.execute("create table date_test (d date)");
        s.close();
        execute(c,dates,gc,true);
        c.close();
    }

    public static final boolean datesEqual(GregorianCalendar gc,java.util.Date d1,java.util.Date d2){
        int year1,year2,month1,month2,date1,date2;
        gc.setTimeInMillis(d1.getTime());
        year1=gc.get(GregorianCalendar.YEAR);
        month1=gc.get(GregorianCalendar.MONTH);
        date1=gc.get(GregorianCalendar.DATE);
        System.out.println("=========");
        System.out.println("hour: "+gc.get(GregorianCalendar.HOUR_OF_DAY));
        System.out.println("minute: "+gc.get(GregorianCalendar.MINUTE));
        System.out.println("second: "+gc.get(GregorianCalendar.SECOND));
        System.out.println("millisecond: "+gc.get(GregorianCalendar.MILLISECOND));

        gc.setTimeInMillis(d2.getTime());
        year2=gc.get(GregorianCalendar.YEAR);
        month2=gc.get(GregorianCalendar.MONTH);
        date2=gc.get(GregorianCalendar.DATE);
        System.out.println("=========");
        System.out.println("hour: "+gc.get(GregorianCalendar.HOUR_OF_DAY));
        System.out.println("minute: "+gc.get(GregorianCalendar.MINUTE));
        System.out.println("second: "+gc.get(GregorianCalendar.SECOND));
        System.out.println("millisecond: "+gc.get(GregorianCalendar.MILLISECOND));
        System.out.println("=========");
        System.out.println("year1: "+year1+"; month1: "+month1+"; date1: "+date1);
        System.out.println("year2: "+year2+"; month2: "+month2+"; date2: "+date2);
        return (date1==date2)&&(month1==month2)&&(year1==year2);
    }

    public static java.util.Date setTimeInDate(GregorianCalendar gc,java.util.Date d,int hourOfDay,int minute,int
second,intmillisecond){ 
        gc.setTimeInMillis(d.getTime());
        gc.set(GregorianCalendar.HOUR_OF_DAY,hourOfDay);
        gc.set(GregorianCalendar.MINUTE,minute);
        gc.set(GregorianCalendar.SECOND,second);
        gc.set(GregorianCalendar.MILLISECOND,millisecond);
        d.setTime(gc.getTimeInMillis());
        return d;
    }


    public static void main(String[] args)throws SQLException,ClassNotFoundException{
        JDBCTest2.execute();
    }
}