Thread: problem with dates when using a java calendar object with a non-default timezone
problem with dates when using a java calendar object with a non-default timezone
From
Jair da Silva Ferreira Jr
Date:
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();
}
}
Hi, Jair, On Wed, 13 Oct 2004 20:26:09 -0300 Jair da Silva Ferreira Jr <j2@amazon.com.br> wrote: > 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? Could you tell us how the table is defined? If you have it defined as "TIMESTAMP WITHOUT TIME ZONE", your dates always get converted to UTC and loose the time zone. HTH, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com
On Wed, 13 Oct 2004, Jair da Silva Ferreira Jr wrote:
> 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.
There are a number of problems going on here:
First the JDBC driver didn't support using an alternate Calendar until
very recently. This functionality is only in the 8.0 series.
Second it doesn't make sense to store a timezone with a date alone. You
need time information as well, so you've selected the wrong pg datatype
"date" and should probably be using "timestamp with time zone".
Finally the javadoc says
To conform with the definition of SQL DATE, the millisecond values
wrapped by a java.sql.Date instance must be 'normalized' by
setting the hours, minutes, seconds, and milliseconds to zero in
the particular time zone with which the instance is associated.
I believe this means your whole setTimeInDate method is not legal.
Kris Jurka
Hi Markus,
Markus Schaber wrote:
>Hi, Jair,
>
>On Wed, 13 Oct 2004 20:26:09 -0300
>Jair da Silva Ferreira Jr <j2@amazon.com.br> wrote:
>
>
>
>>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?
>>
>>
>
>Could you tell us how the table is defined? If you have it defined as
>"TIMESTAMP WITHOUT TIME ZONE", your dates always get converted to UTC
>and loose the time zone.
>
>
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?
Thanks,
Jair Jr
>HTH,
>Markus
>
>
>
>
>
Hi Kris,
Kris Jurka wrote:
>On Wed, 13 Oct 2004, Jair da Silva Ferreira Jr wrote:
>
>
>
>>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.
>>
>>
>
>There are a number of problems going on here:
>
>First the JDBC driver didn't support using an alternate Calendar until
>very recently. This functionality is only in the 8.0 series.
>
>
If the Calendar is not fully supported, in what timezone should my
dates be so that this problem does not happen? UTC? The default JVM
timezone?
>Second it doesn't make sense to store a timezone with a date alone. You
>need time information as well, so you've selected the wrong pg datatype
>"date" and should probably be using "timestamp with time zone".
>
>
I am not trying to store the timezone in the database. I am just
trying to tell the jdbc driver that the date I am inserting or selecting
is in the specified timezone.
>Finally the javadoc says
>
> To conform with the definition of SQL DATE, the millisecond values
> wrapped by a java.sql.Date instance must be 'normalized' by
> setting the hours, minutes, seconds, and milliseconds to zero in
> the particular time zone with which the instance is associated.
>
>I believe this means your whole setTimeInDate method is not legal.
>
>
The setTimeInDate method is exactly trying to verify if the driver
is normalizing the date correctly. The algorithm is to set a time in the
date, insert it into the database and select it back hoping that the
driver sets the hours, minutes, seconds, and milliseconds to 0. But,
unfortunately, they are not 0, and, sometimes even the day of the date
is wrong. This is exactly the problem I'm trying to explain.
I analysed the AbstractJdbc2Statement.java and
AbstractJdbc2ResultSet.java source code and noticed that you transform
the date in a String and them insert 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
>Kris Jurka
>
>.
>
>
>
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
As Kris already pointed out you can use the calendar specified by the user now; if you download the development driver. 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) > > -- Dave Cramer www.postgresintl.com 519 939 0336 ICQ#14675561
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)
>>
>>
>
Jair, When using the newer drivers, did you use setDate( ) with the appropriate calendar object ? If you have test code, can you send it ? 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 > > -- Dave Cramer www.postgresintl.com 519 939 0336 ICQ#14675561
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();
}
}