Re: problem with dates when using a java calendar object with - Mailing list pgsql-jdbc

Dave Cramer wrote:

> Jair,
>
> When using the newer drivers, did you use setDate( ) with the
> appropriate calendar object ?

    Yes, I am passing the Calendar object to the setDate() method.

>
> If you have test code, can you send it ?

    Yes, I have test code. It is attached in this email.

    Am I doing something wrong in my test code?

Thanks,
     Jair Jr

>
> Dave
>
> Jair da Silva Ferreira Jr wrote:
>
>> Hi Dave,
>>    Thanks for your reply.
>>
>> Dave Cramer wrote:
>>
>>> As Kris already pointed out you can use the calendar specified by
>>> the user now; if you download the development driver.
>>
>>
>>
>>    I have used the following drivers in my test program:
>> pg72jdbc2.jar, pgdev.306.jdbc2.jar and pgdev.306.jdbc3.jar. All of
>> them presented the problem but with different time errors. The test
>> results are above. I am using a DateFormat to print dates. The
>> DateFormat object is appropriately configured for my timezone and
>> calendar. Any other ideas? :-)
>>
>> Using driver: pg72jdbc2.jar
>> date (0) inserted: 16/10/04 00:00
>> date (1) inserted: 16/10/04 23:59
>> date (2) inserted: 16/10/04 08:00
>> date (3) inserted: 16/10/04 19:00
>> date (0) loaded: 15/10/04 17:00
>> date (1) loaded: 16/10/04 17:00
>> date (2) loaded: 15/10/04 17:00
>> date (3) loaded: 16/10/04 17:00
>>
>> Using driver: pgdev.306.jdbc2.jar
>> date (0) inserted: 16/10/04 00:00
>> date (1) inserted: 16/10/04 23:59
>> date (2) inserted: 16/10/04 08:00
>> date (3) inserted: 16/10/04 19:00
>> date (0) loaded: 15/10/04 10:00
>> date (1) loaded: 16/10/04 10:00
>> date (2) loaded: 15/10/04 10:00
>> date (3) loaded: 16/10/04 10:00
>>
>> Using driver: pgdev.306.jdbc3.jar
>> date (0) inserted: 16/10/04 00:00
>> date (1) inserted: 16/10/04 23:59
>> date (2) inserted: 16/10/04 08:00
>> date (3) inserted: 16/10/04 19:00
>> date (0) loaded: 15/10/04 10:00
>> date (1) loaded: 16/10/04 10:00
>> date (2) loaded: 15/10/04 10:00
>> date (3) loaded: 16/10/04 10:00
>>
>> Thanks,
>>    Jair Jr
>>
>>>
>>> Dave
>>>
>>> Jair da Silva Ferreira Jr wrote:
>>>
>>>> Hi Markus,
>>>>    Thank you very much for your reply.
>>>>
>>>> Markus Schaber wrote:
>>>>
>>>>> Hi, Jair,
>>>>>
>>>>> On Thu, 14 Oct 2004 11:41:21 -0300
>>>>> Jair da Silva Ferreira Jr <j2@amazon.com.br> wrote:
>>>>>
>>>>>
>>>>>
>>>>>> My test table definition is: create table date_test (d date)
>>>>>> As you can see it is a date type, so there's no time zone
>>>>>> information on it.
>>>>>> Do you think that the date not having time zone information is
>>>>>> the source of the problem? If yes, what time zone should I use so
>>>>>> that dates are correctly inserted and selected? UTC time zone?
>>>>>> The default JVM timezone?
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> It is possible that this is the source of your problem.
>>>>>
>>>>> Table columns with time zone always remember the time zone of the
>>>>> dates
>>>>> stored, while the table columns without time zone convert your
>>>>> date to
>>>>> UTC and forget the original time zone.
>>>>>
>>>>> This had to be changed at postgres level. Just test whether manually
>>>>> inserting dates (e. G. via psql) and then re-reading works.
>>>>>
>>>>>
>>>>    I inserted dates via pgsql and selected them via Java and the
>>>> problem remains. I keep getting wrong days in my date. I think the
>>>> JDBC driver is somehow considering the current jvm timezone to
>>>> calculate dates. I think this is wrong because I am providing a
>>>> user-defined Calendar exactly not to use the jvm default.
>>>>    I analysed the AbstractJdbc2Statement.java and
>>>> AbstractJdbc2ResultSet.java source code and I noticed that the
>>>> driver transforms the date in a String and them inserts it into the
>>>> database. Maybe the problem is how the String is being generated. I
>>>> think the best solution is to use a java.text.DateFormat object
>>>> with the calendar specified by the user set on it. This DateFormat
>>>> object could be used to both insert and select the date value from
>>>> the database because it has a format(Date) and parse(Date) method.
>>>> What do you think?
>>>>
>>>> Thanks,
>>>>    Jair Jr
>>>>
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 2: you can get off all lists at once with the unregister command
>>>>    (send "unregister YourEmailAddressHere" to
>>>> majordomo@postgresql.org)
>>>>
>>>>
>>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index scan if
>> your
>>      joining column's datatypes do not match
>>
>>
>

import java.sql.*;

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

public class JDBCTest2 {

    public static void execute(Connection c,List dates,GregorianCalendar cal,boolean useCalendarInJDBC)throws
SQLException{
        DateFormat df=new SimpleDateFormat();
        df.setCalendar(cal);
        df.setTimeZone(cal.getTimeZone());
        int j=0;
        PreparedStatement ps=c.prepareStatement("insert into date_test values (?)");
        for(Iterator it=dates.iterator();it.hasNext();){
            java.sql.Date d=(java.sql.Date)it.next();
            if(useCalendarInJDBC){
                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(useCalendarInJDBC){
                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();
    }
}