Postgres Pro
Downloads
Home   >   mailing lists

Thread: JDBC date problem

JDBC date problem

From
"Mike Cannon-Brookes"
Date:
I'm having a problem with dates in the JDBC driver. They are being mangled
when they come through from the database, here are some examples:

> Postgres:            Java:
> 2000-12-20 16:11:00+11    Nov 20 16:00:00 EST 2000

(looks like it's pulling the month from the timezone or minute field?)

It gets worse ;)

> Postgres:            Java:
> 2001-01-03 17:35:00+11    Nov 03 17:00:00 EST 2003

(again, month comes from the timezone field, but 2004?! ;))

And worse still...

> Postgres:            Java:
> 2001-01-02 18:41:00+11    May 02 17:00:00 EST 2004

(now I'm not really sure where anything came from here, besides the day -
every other number seems wrong?)

Some raw data:
Postgres:             Java:
2000-12-20 16:11:00+11 | 2000-11-20 16:00:00
2001-01-02 18:41:00+11 | 2004-05-02 17:00:00
2001-01-03 17:35:00+11 | 2003-11-03 17:00:00
2000-12-20 15:00:00+11 | 1999-12-20 15:00:00
2001-01-04 19:00:00+11 | 2000-12-04 19:00:00
2001-01-09 11:00:00+11 | 2000-12-09 11:00:00

Any ideas? This is a real stumper and I need to find a solution asap! I'm
using a JDBC driver I built from CVS no more than a month ago.

-mike
____________________________________________________________
Mike Cannon-Brookes - Technology Director, Asia Pacific
internet.com Corporation - The Internet Industry Portal
Ph: (612) 9290 1088 - Mob: 0416 106090

- The Media Network @ http://australia.internet.com
- Meet A Guru @ http://www.breakfastforums.com.au
- Subscribe Online @ http://www.enewsletters.com.au
____________________________________________________________

Re: JDBC date problem

From
Michael Stephenson
Date:
> Any ideas? This is a real stumper and I need to find a solution asap! I'm
> using a JDBC driver I built from CVS no more than a month ago.

The results look to me like that in org.postgresql.jdbc2.ResultSet
'mm' and 'MM' have been confused (I know that this was the case in a patch
I submitted), try replacing getTimeStamp() with:
 public Timestamp getTimestamp(int columnIndex) throws SQLException {   String s = getString(columnIndex);
if(s==null)returnnull;
 
   SimpleDateFormat df = null;   if (s.length()>21 && s.indexOf('.') != -1) {       df = new
SimpleDateFormat("yyyy-MM-ddHH:mm:ss.SSzzz");   } else if (s.length()>19 && s.indexOf('.') == -1) {       df = new
SimpleDateFormat("yyyy-MM-ddHH:mm:sszzz");   } else if (s.length()>19 && s.indexOf('.') != -1) {df = new
SimpleDateFormat("yyyy-MM-ddHH:mm:ss.SS");   } else if (s.length()>10 && s.length()<=18) {df = new
SimpleDateFormat("yyyy-MM-ddHH:mm:ss");   } else {       df = new SimpleDateFormat("yyyy-MM-dd");   }
 
   try {return new Timestamp(df.parse(s).getTime());   } catch(ParseException e) {throw new
PSQLException("postgresql.res.badtimestamp",new                              Integer(e.getErrorOffset()),s);   } }
 

Michael Stephenson

RE: JDBC date problem

From
"Mike Cannon-Brookes"
Date:
For reference, I just updated my CVS tree and recompiled using Ant and the
new jar works much better. It must be a bug in the old jar (ResultSet.java
was indeed updated with your patch Michael).

Thanks for the tip!
Mike

PS For anyone interested, org.postgresql.xa.Test has a serious bug in it,
tries to instantiate an interface (XADataSource) - someone might want to
hack at it. (just comment out the method and recompile if you care not about
testing ;))

> -----Original Message-----
> From: pgsql-interfaces-owner@postgresql.org
> [mailto:pgsql-interfaces-owner@postgresql.org]On Behalf Of Michael
> Stephenson
> Sent: Wednesday, January 10, 2001 8:42 PM
> To: Pgsql-Interfaces@Postgresql. Org
> Subject: Re: [INTERFACES] JDBC date problem
>
>
> > Any ideas? This is a real stumper and I need to find a solution
> asap! I'm
> > using a JDBC driver I built from CVS no more than a month ago.
>
> The results look to me like that in org.postgresql.jdbc2.ResultSet
> 'mm' and 'MM' have been confused (I know that this was the case in a patch
> I submitted), try replacing getTimeStamp() with:
>
>   public Timestamp getTimestamp(int columnIndex) throws SQLException
>   {
>     String s = getString(columnIndex);
>     if(s==null)
>     return null;
>
>     SimpleDateFormat df = null;
>     if (s.length()>21 && s.indexOf('.') != -1) {
>         df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
>     } else if (s.length()>19 && s.indexOf('.') == -1) {
>         df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzz");
>     } else if (s.length()>19 && s.indexOf('.') != -1) {
>     df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SS");
>     } else if (s.length()>10 && s.length()<=18) {
>     df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
>     } else {
>         df = new SimpleDateFormat("yyyy-MM-dd");
>     }
>
>     try {
>     return new Timestamp(df.parse(s).getTime());
>     } catch(ParseException e) {
>     throw new PSQLException("postgresql.res.badtimestamp",new
>                                 Integer(e.getErrorOffset()),s);
>     }
>   }
>
> Michael Stephenson
>
>
>

RE: JDBC date problem

From
"Mike Cannon-Brookes"
Date:
Actually I've been playing around more and there's some cases left out of
the driver. I hacked it to put them in and it works much better now. (Was
giving Bad Timestamp exceptions).

I've attached the hacked / fixed ResultSet.java file as I have no CVS
access, would some kind soul with access patch it? I've marked my changes
clearly with comments in the getTimestamp() method.

Hope this helps!

Mike

> -----Original Message-----
> From: pgsql-interfaces-owner@postgresql.org
> [mailto:pgsql-interfaces-owner@postgresql.org]On Behalf Of Mike
> Cannon-Brookes
> Sent: Thursday, January 11, 2001 12:20 AM
> To: Pgsql-Interfaces@Postgresql. Org
> Subject: RE: [INTERFACES] JDBC date problem
>
>
> For reference, I just updated my CVS tree and recompiled using Ant and the
> new jar works much better. It must be a bug in the old jar (ResultSet.java
> was indeed updated with your patch Michael).
>
> Thanks for the tip!
> Mike
>
> PS For anyone interested, org.postgresql.xa.Test has a serious bug in it,
> tries to instantiate an interface (XADataSource) - someone might want to
> hack at it. (just comment out the method and recompile if you
> care not about
> testing ;))
>
> > -----Original Message-----
> > From: pgsql-interfaces-owner@postgresql.org
> > [mailto:pgsql-interfaces-owner@postgresql.org]On Behalf Of Michael
> > Stephenson
> > Sent: Wednesday, January 10, 2001 8:42 PM
> > To: Pgsql-Interfaces@Postgresql. Org
> > Subject: Re: [INTERFACES] JDBC date problem
> >
> >
> > > Any ideas? This is a real stumper and I need to find a solution
> > asap! I'm
> > > using a JDBC driver I built from CVS no more than a month ago.
> >
> > The results look to me like that in org.postgresql.jdbc2.ResultSet
> > 'mm' and 'MM' have been confused (I know that this was the case
> in a patch
> > I submitted), try replacing getTimeStamp() with:
> >
> >   public Timestamp getTimestamp(int columnIndex) throws SQLException
> >   {
> >     String s = getString(columnIndex);
> >     if(s==null)
> >     return null;
> >
> >     SimpleDateFormat df = null;
> >     if (s.length()>21 && s.indexOf('.') != -1) {
> >         df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
> >     } else if (s.length()>19 && s.indexOf('.') == -1) {
> >         df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzz");
> >     } else if (s.length()>19 && s.indexOf('.') != -1) {
> >     df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SS");
> >     } else if (s.length()>10 && s.length()<=18) {
> >     df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> >     } else {
> >         df = new SimpleDateFormat("yyyy-MM-dd");
> >     }
> >
> >     try {
> >     return new Timestamp(df.parse(s).getTime());
> >     } catch(ParseException e) {
> >     throw new PSQLException("postgresql.res.badtimestamp",new
> >                                 Integer(e.getErrorOffset()),s);
> >     }
> >   }
> >
> > Michael Stephenson
> >
> >
> >
>
>
Attachment

Re: JDBC date problem

From
Bruce Momjian
Date:
Would you please send me a context diff (diff -c) comparing the two
versions?  Thanks.


> Actually I've been playing around more and there's some cases left out of
> the driver. I hacked it to put them in and it works much better now. (Was
> giving Bad Timestamp exceptions).
> 
> I've attached the hacked / fixed ResultSet.java file as I have no CVS
> access, would some kind soul with access patch it? I've marked my changes
> clearly with comments in the getTimestamp() method.
> 
> Hope this helps!
> 
> Mike
> 
> > -----Original Message-----
> > From: pgsql-interfaces-owner@postgresql.org
> > [mailto:pgsql-interfaces-owner@postgresql.org]On Behalf Of Mike
> > Cannon-Brookes
> > Sent: Thursday, January 11, 2001 12:20 AM
> > To: Pgsql-Interfaces@Postgresql. Org
> > Subject: RE: [INTERFACES] JDBC date problem
> >
> >
> > For reference, I just updated my CVS tree and recompiled using Ant and the
> > new jar works much better. It must be a bug in the old jar (ResultSet.java
> > was indeed updated with your patch Michael).
> >
> > Thanks for the tip!
> > Mike
> >
> > PS For anyone interested, org.postgresql.xa.Test has a serious bug in it,
> > tries to instantiate an interface (XADataSource) - someone might want to
> > hack at it. (just comment out the method and recompile if you
> > care not about
> > testing ;))
> >
> > > -----Original Message-----
> > > From: pgsql-interfaces-owner@postgresql.org
> > > [mailto:pgsql-interfaces-owner@postgresql.org]On Behalf Of Michael
> > > Stephenson
> > > Sent: Wednesday, January 10, 2001 8:42 PM
> > > To: Pgsql-Interfaces@Postgresql. Org
> > > Subject: Re: [INTERFACES] JDBC date problem
> > >
> > >
> > > > Any ideas? This is a real stumper and I need to find a solution
> > > asap! I'm
> > > > using a JDBC driver I built from CVS no more than a month ago.
> > >
> > > The results look to me like that in org.postgresql.jdbc2.ResultSet
> > > 'mm' and 'MM' have been confused (I know that this was the case
> > in a patch
> > > I submitted), try replacing getTimeStamp() with:
> > >
> > >   public Timestamp getTimestamp(int columnIndex) throws SQLException
> > >   {
> > >     String s = getString(columnIndex);
> > >     if(s==null)
> > >     return null;
> > >
> > >     SimpleDateFormat df = null;
> > >     if (s.length()>21 && s.indexOf('.') != -1) {
> > >         df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
> > >     } else if (s.length()>19 && s.indexOf('.') == -1) {
> > >         df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzz");
> > >     } else if (s.length()>19 && s.indexOf('.') != -1) {
> > >     df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SS");
> > >     } else if (s.length()>10 && s.length()<=18) {
> > >     df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> > >     } else {
> > >         df = new SimpleDateFormat("yyyy-MM-dd");
> > >     }
> > >
> > >     try {
> > >     return new Timestamp(df.parse(s).getTime());
> > >     } catch(ParseException e) {
> > >     throw new PSQLException("postgresql.res.badtimestamp",new
> > >                                 Integer(e.getErrorOffset()),s);
> > >     }
> > >   }
> > >
> > > Michael Stephenson
> > >
> > >
> > >
> >
> >

[ Attachment, skipping... ]


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026