Re: help with front/backend datatype converting

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

> >