Skip site navigation (1) Skip section navigation (2)

Re: help with front/backend datatype converting

From: "Do, Leon \(Leon\)" <leondo(at)lucent(dot)com>
To: "Dave Cramer" <pg(at)fastcrypt(dot)com>, "imad" <immaad(at)gmail(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: help with front/backend datatype converting
Date: 2006-11-14 16:52:04
Message-ID: D1EE06BA46B1E4449AF9A4F2FBEE1861285FED@ILEXC2U01.ndc.lucent.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Dave,

 

There are actually bugs in the Postgres timestamp string.  See below.

 

> -----Original Message-----

> From: Dave Cramer [mailto:pg(at)fastcrypt(dot)com]

> Sent: Saturday, November 11, 2006 8:00 AM

> To: imad

> Cc: Do, Leon (Leon); pgsql-jdbc(at)postgresql(dot)org

> Subject: Re: [JDBC] help with front/backend datatype converting

> 

> 

> On 11-Nov-06, at 6:40 AM, imad wrote:

> 

> > On 11/11/06, Do, Leon (Leon) <leondo(at)lucent(dot)com> wrote:

> >>

> >>

> >>

> >>

> >>

> >>

> >> Dave,

> >>

> >>

> >>

> >> I see you raised a lot of issues about this problem but I don't

> >> have any

> >> suggestion as of now.  I'll think about it.

> >>

> >>

> >>

> >> On the side, the timezone for timestamp is always sending in this

> >> format:

> >>

> >> 2006-11-10 14:36:19.213000 -0500

> >>

> >>

> >>

> >> should it be

> >>

> >> 2006-11-10 14:36:19.213000 -05:00

> >

> > this is better.

> Why ? 0500 is perfectly acceptable to postgres

[Do, Leon (Leon)] 

 

I think postgres has a few bugs in this area.    When timezone doesn't
have a semicolon between hour and min, TimestampUtil.java cann't handle
it.   The firstNonDigits method call below returns all four digits and
assigns them to timezone hour variable.

 

In the TimestampUtils.java

            // Possibly read timezone.

            sep = charAt(s, start);

            if (sep == '-' || sep == '+') {

                int tzsign = (sep == '-') ? -1 : 1;

                int tzhr, tzmin;

    

                end = firstNonDigit(s, start+1);    // Skip +/-

                tzhr = number(s, start+1, end);

                start = end;

    

                sep = charAt(s, start);

                if (sep == ':') {

                    end = firstNonDigit(s, start+1);  // Skip ':'

                    tzmin = number(s, start+1, end);

                    start = end;

                } else {

                    tzmin = 0;

                }

 

 

And for the second bugs, fractional part of the second (e.g.
14:36:19.213000) seem to be interpreted incorrectly when converting to
Java Date.  213000 would round up as additional 3.52 mins. 

Postgres timestamp

2006-11-10 14:36:19.213000 -05:00

 

to Java Date

2006-11-10 14:39:52.0 -05:00

 

Would somebody help to fix these bugs?

 

thanks

 

Leon Do

 

 

> >

> >

> > --Imad

> > www.EnterpriseDB.com

> >

> > ---------------------------(end of

> > broadcast)---------------------------

> > TIP 7: You can help support the PostgreSQL project by donating at

> >

> >                http://www.postgresql.org/about/donate

> >

 

In response to

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2006-11-14 23:05:53
Subject: Re: help with front/backend datatype converting
Previous:From: surabhi.ahujaDate: 2006-11-14 08:27:20
Subject: JDBC driver for PostgreSQL 8.1.5

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group