Dave,
There are actually bugs in the Postgres timestamp string. See below.
> -----Original Message-----
> From: Dave Cramer [mailto:pg@fastcrypt.com]
> Sent: Saturday, November 11, 2006 8:00 AM
> To: imad
> Cc: Do, Leon (Leon); pgsql-jdbc@postgresql.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@lucent.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
> >
