Re: problem with dates when using a java calendar object with
От | Jair da Silva Ferreira Jr |
---|---|
Тема | Re: problem with dates when using a java calendar object with |
Дата | |
Msg-id | 4173BD9C.1010908@amazon.com.br обсуждение исходный текст |
Ответ на | Re: problem with dates when using a java calendar object with (Dave Cramer <pg@fastcrypt.com>) |
Список | 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(); } }
В списке pgsql-jdbc по дате отправления: