Re: problem with dates when using a java calendar object with

From: Jair da Silva Ferreira Jr <j2(at)amazon(dot)com(dot)br>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: problem with dates when using a java calendar object with
Date: 2004-10-14 23:33:37
Message-ID: 416F0CD1.8050202@amazon.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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
>
>.
>
>
>

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Jair da Silva Ferreira Jr 2004-10-14 23:33:59 Re: problem with dates when using a java calendar object with
Previous Message Dave Cramer 2004-10-14 23:08:31 Re: Mining SQLException for info