Hi,



It looks like PostgreSQL JDBC always converting a column value to text
before sending it to the Backend server. Is there a way to stop this
behavior?



I am using the following piece of code to set the timestamp value:



ByteArrayInputStream is = new ByteArrayInputStream(bdata);



prepstmt.setBinaryStream(1,is,bdata.length);



java.sql.Timestamp sqltime = new


java.sql.Timestamp(System.currentTimeMillis());

prepstmt.setTimestamp(2,sqltime);





When the backend server receives the parse message, the oid type for
binary data is set but the oid type for timestamp is not set.



When the bind message arrives, the format type for binary data is binary
and the format type for timestamp is text and there is no way to know
what is the type of the value anymore. Can I configure to disable text
converting feature?



thanks





Leon Do

Lucent Technologies

Mobility Software Engineer

Hi Leon,

No, currently there is no way to do this. It is possible, but this is
probably the worst data type to try this with. Postgresql has two
timestamp types, 1 with timezone, 1 without, jdbc has no knowledge of
this. Do you have suggestions on how to resolve this ? Also consider
the actual timezone, what if the timezone of the server is different
than the timezone of the client. To add to the problem, it is
possible for the server to keep dates/timestamps as 64bit integers,
or Floating point values. The client has to send the data in exactly
the right format.

Sorry to only outline the problems without constructive solutions.

Dave

On 10-Nov-06, at 3:58 PM, Do, Leon (Leon) wrote:

> Hi,
>
>
>
> It looks like PostgreSQL JDBC always converting a column value to
> text before sending it to the Backend server. Is there a way to
> stop this behavior?
>
>
>
> I am using the following piece of code to set the timestamp value:
>
>
>
> ByteArrayInputStream is = new ByteArrayInputStream(bdata);
>
>
>
> prepstmt.setBinaryStream(1,is,bdata.length);
>
>
>
> java.sql.Timestamp sqltime = new
>
> java.sql.Timestamp
> (System.currentTimeMillis());
>
> prepstmt.setTimestamp(2,sqltime);
>
>
>
>
>
> When the backend server receives the parse message, the oid type
> for binary data is set but the oid type for timestamp is not set.
>
>
>
> When the bind message arrives, the format type for binary data is
> binary and the format type for timestamp is text and there is no
> way to know what is the type of the value anymore. Can I
> configure to disable text converting feature?
>
>
>
> thanks
>
>
>
>
>
> Leon Do
>
> Lucent Technologies
>
> Mobility Software Engineer
>
>
>
>
>
>

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



with the colon between hour and min?



thanks again



Leon Do











________________________________

From: Dave Cramer [mailtog@fastcrypt.com]
Sent: Friday, November 10, 2006 4:15 PM
To: Do, Leon (Leon)
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] help with front/backend datatype converting



Hi Leon,



No, currently there is no way to do this. It is possible, but this is
probably the worst data type to try this with. Postgresql has two
timestamp types, 1 with timezone, 1 without, jdbc has no knowledge of
this. Do you have suggestions on how to resolve this ? Also consider the
actual timezone, what if the timezone of the server is different than
the timezone of the client. To add to the problem, it is possible for
the server to keep dates/timestamps as 64bit integers, or Floating point
values. The client has to send the data in exactly the right format.



Sorry to only outline the problems without constructive solutions.



Dave



On 10-Nov-06, at 3:58 PM, Do, Leon (Leon) wrote:





Hi,



It looks like PostgreSQL JDBC always converting a column value to text
before sending it to the Backend server. Is there a way to stop this
behavior?



I am using the following piece of code to set the timestamp value:



new

prepstmt.setBinaryStream(1,is,bdata.); java.sql.Timestamp
sqltime =



When the backend server receives the parse message, the oid type for
binary data is set but the oid type for timestamp is not set.

When the bind message arrives, the format type for binary data is binary
and the format type for timestamp is text and there is no way to know
what is the type of the value anymore. Can I configure to disable text
converting feature?

thanks





Leon Do

Lucent Technologies

Mobility Software Engineer

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.


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

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Dave,



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



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

> From: Dave Cramer [mailtog@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

> >

Do, Leon (Leon) wrote:

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

Does the server ever generate timezones like this?

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

Testcase please?

-O

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

> -----Original Message-----
> From: Oliver Jowett [mailtoliver@opencloud.com]
> Sent: Tuesday, November 14, 2006 6:06 PM
> To: Do, Leon (Leon)
> Cc: Dave Cramer; imad; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] help with front/backend datatype converting
>
> Do, Leon (Leon) wrote:
>
> > 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.
>
> Does the server ever generate timezones like this?
I haven't tested the server side but I know the client generates that
format but it didn't take what it generated.
>
> > 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.

>
> Testcase please?
Sorry, this was my mistake when using java SimpleDateFormatter class and
assumed the second fractional part as millisecond instead of nanosecond.

thanks
Leon Do
>
> -O

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

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

Do, Leon (Leon) wrote:

> I haven't tested the server side but I know the client generates that
> format but it didn't take what it generated.

Well, in general we only care about parsing what the server generates,
not what the driver generates.

One exception might be updateable resultsets.

The solution is probably to fix the generation side so we generate the
same format as the server produces.

-O

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Fri, 17 Nov 2006, Oliver Jowett wrote:

> Do, Leon (Leon) wrote:
>
>> I haven't tested the server side but I know the client generates that
>> format but it didn't take what it generated.
>
> Well, in general we only care about parsing what the server generates, not
> what the driver generates.
>
> One exception might be updateable resultsets.
>
> The solution is probably to fix the generation side so we generate the same
> format as the server produces.
>

I have fixed this in CVS for 8.0, 8.1, 8.2, and 8.3dev.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match