Thread: help with front/backend datatype converting

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

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
>

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

> -----Original Message-----
> From: Oliver Jowett [mailto:oliver@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

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

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

It would be nice if you can add a support for parsing hh:mm as well as
hhmm to conform to ISO8601.  There are many other uses of this class.  I
sent the changes earlier.

Thanks

Leon Do

> -----Original Message-----
> From: Kris Jurka [mailto:books@ejurka.com]
> Sent: Thursday, January 04, 2007 7:35 PM
> To: Oliver Jowett
> Cc: Do, Leon (Leon); Dave Cramer; imad; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] help with front/backend datatype converting
>
>
>
> 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
>

On Fri, 5 Jan 2007, Do, Leon (Leon) wrote:

> It would be nice if you can add a support for parsing hh:mm as well as
> hhmm to conform to ISO8601.  There are many other uses of this class.  I
> sent the changes earlier.
>

This class is not part of any public API and is intended for usage by the
driver only.  We have no desire to add or maintain code beyond what is
necessary for the driver.  You are certainly free to copy and reuse this
code under the BSD license, but you will have to maintain your own code
changes unless you can demonstrate a usefulness for them within the
driver.

Kris Jurka

> -----Original Message-----
> From: Kris Jurka [mailto:books@ejurka.com]
> Sent: Friday, January 05, 2007 12:18 PM
> To: Do, Leon (Leon)
> Cc: Oliver Jowett; Dave Cramer; imad; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] help with front/backend datatype converting
>
>
>
> On Fri, 5 Jan 2007, Do, Leon (Leon) wrote:
>
> > It would be nice if you can add a support for parsing hh:mm
> as well as
> > hhmm to conform to ISO8601.  There are many other uses of
> this class.
> > I sent the changes earlier.
> >
>
> This class is not part of any public API and is intended for
> usage by the driver only.  We have no desire to add or
> maintain code beyond what is necessary for the driver.  You
> are certainly free to copy and reuse this code under the BSD
> license, but you will have to maintain your own code changes
> unless you can demonstrate a usefulness for them within the driver.

Leon-it is actually a bug in the class.  The server could possibly sends
a timezone in 'hhmm' format and the class treats the entire 4 digits as
number of hours which is totally incorrect.  I already provided the
quick fix so it won't take too much effort to put it in the load.  If
you tell me the direction then I can add it in the load so you don't
have to spend extra effort.

>
> Kris Jurka
>

On Fri, 5 Jan 2007, Do, Leon (Leon) wrote:

> Leon-it is actually a bug in the class.  The server could possibly sends
> a timezone in 'hhmm' format and the class treats the entire 4 digits as
> number of hours which is totally incorrect.  I already provided the
> quick fix so it won't take too much effort to put it in the load.  If
> you tell me the direction then I can add it in the load so you don't
> have to spend extra effort.
>

Please demonstrate how it is possible to generate a timezone in this
format.  I can't seem to do it:

jurka=# set timezone='Asia/Tehran';
SET
jurka=# select now();
                now
----------------------------------
  2007-01-05 22:56:26.065258+03:30


Kris Jurka

Postgresql8.1 documentation:

Table 8-12. Time Zone Input
Example    Description
PST    Pacific Standard Time
-8:00    ISO-8601 offset for PST
-800    ISO-8601 offset for PST
-8    ISO-8601 offset for PST
zulu    Military abbreviation for UTC
z    Short form of zulu

It can takes any input.  You cann't assume it only return hh:mm.  When
things can go wrong it will go wrong.




Refer to Appendix B for a list of time zone names that are recognized
for input.

> -----Original Message-----
> From: Kris Jurka [mailto:books@ejurka.com]
> Sent: Friday, January 05, 2007 2:52 PM
> To: Do, Leon (Leon)
> Cc: Oliver Jowett; Dave Cramer; imad; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] help with front/backend datatype converting
>
>
>
> On Fri, 5 Jan 2007, Do, Leon (Leon) wrote:
>
> > Leon-it is actually a bug in the class.  The server could possibly
> > sends a timezone in 'hhmm' format and the class treats the entire 4
> > digits as number of hours which is totally incorrect.  I already
> > provided the quick fix so it won't take too much effort to
> put it in
> > the load.  If you tell me the direction then I can add it
> in the load
> > so you don't have to spend extra effort.
> >
>
> Please demonstrate how it is possible to generate a timezone
> in this format.  I can't seem to do it:
>
> jurka=# set timezone='Asia/Tehran';
> SET
> jurka=# select now();
>                 now
> ----------------------------------
>   2007-01-05 22:56:26.065258+03:30
>

The Postgresql 8.1 document indicates that it can take the following
inputs:
Table 8-12. Time Zone Input
Example    Description
PST    Pacific Standard Time
-8:00    ISO-8601 offset for PST
-800    ISO-8601 offset for PST
-8    ISO-8601 offset for PST


The driver should expect timezone output to be one of its form as well.
Murphy's Law, "If anything can go wrong, it will" so we cann't hardcode
that way forever.

In my situation, I have to work with multiple DBs.  Since timestamp data
type is varied across different DB vendors therefore I have to use the
varchar to store the timestamp string.

Ex:
create table test {
timestampfield varchar(50)
);


Insert into test values ("2007-01-05 23:20:25.122222+06:30");
Insert into test values ("2006-01-05 23:20:25.122222+0650");

I must allow two forms of timezones as indicated in the document

Using PG JDBC driver, I can do the following:

ResultSet rs = stmt.getResultSet();
rs.getTimestamp();

That the way I use it but it is a valid according to the document.

Leon Do


>
> Kris Jurka
>
>

If you are reading the email you'll see paragraphs at the beginning.
Please ignore them and go to the embeded responses later in the page.

Leon Do

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Do, Leon (Leon)
> Sent: Friday, January 05, 2007 4:46 PM
> To: Kris Jurka
> Cc: Oliver Jowett; Dave Cramer; imad; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] help with front/backend datatype converting
>
> Postgresql8.1 documentation:
>
> Table 8-12. Time Zone Input
> Example    Description
> PST    Pacific Standard Time
> -8:00    ISO-8601 offset for PST
> -800    ISO-8601 offset for PST
> -8    ISO-8601 offset for PST
> zulu    Military abbreviation for UTC
> z    Short form of zulu
>
> It can takes any input.  You cann't assume it only return
> hh:mm.  When things can go wrong it will go wrong.
>
>
>
>
> Refer to Appendix B for a list of time zone names that are
> recognized for input.
>
> > -----Original Message-----
> > From: Kris Jurka [mailto:books@ejurka.com]
> > Sent: Friday, January 05, 2007 2:52 PM
> > To: Do, Leon (Leon)
> > Cc: Oliver Jowett; Dave Cramer; imad; pgsql-jdbc@postgresql.org
> > Subject: Re: [JDBC] help with front/backend datatype converting
> >
> >
> >
> > On Fri, 5 Jan 2007, Do, Leon (Leon) wrote:
> >
> > > Leon-it is actually a bug in the class.  The server could
> possibly
> > > sends a timezone in 'hhmm' format and the class treats
> the entire 4
> > > digits as number of hours which is totally incorrect.  I already
> > > provided the quick fix so it won't take too much effort to
> > put it in
> > > the load.  If you tell me the direction then I can add it
> > in the load
> > > so you don't have to spend extra effort.
> > >
> >
> > Please demonstrate how it is possible to generate a
> timezone in this
> > format.  I can't seem to do it:
> >
> > jurka=# set timezone='Asia/Tehran';
> > SET
> > jurka=# select now();
> >                 now
> > ----------------------------------
> >   2007-01-05 22:56:26.065258+03:30
> >
>
> The Postgresql 8.1 document indicates that it can take the following
> inputs:
> Table 8-12. Time Zone Input
> Example    Description
> PST    Pacific Standard Time
> -8:00    ISO-8601 offset for PST
> -800    ISO-8601 offset for PST
> -8    ISO-8601 offset for PST
>
>
> The driver should expect timezone output to be one of its
> form as well.
> Murphy's Law, "If anything can go wrong, it will" so we
> cann't hardcode that way forever.
>
> In my situation, I have to work with multiple DBs.  Since
> timestamp data type is varied across different DB vendors
> therefore I have to use the varchar to store the timestamp string.
>
> Ex:
> create table test {
> timestampfield varchar(50)
> );
>
>
> Insert into test values ("2007-01-05 23:20:25.122222+06:30");
> Insert into test values ("2006-01-05 23:20:25.122222+0650");
>
> I must allow two forms of timezones as indicated in the document
>
> Using PG JDBC driver, I can do the following:
>
> ResultSet rs = stmt.getResultSet();
> rs.getTimestamp();
>
> That the way I use it but it is a valid according to the document.
>
> Leon Do
>
>
> >
> > Kris Jurka
> >
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>

"Do, Leon \(Leon\)" <leondo@alcatel-lucent.com> writes:
> It can takes any input.  You cann't assume it only return hh:mm.

Yes you can --- see EncodeTimezone().  I concur with Kris' opinion
that the driver need not cater for output formats the server will
never generate.  Especially not if it's explicitly setting DateStyle,
as I seem to remember it does.

One thing that possibly *does* need fixed as of 8.2 is that the server
can generate hh:mm:ss if the timezone has a fractional-minute UTC
offset.  I do not believe there are any modern timezones that do, but
quite a lot do if you check dates 100 or so years ago.

regression=# set timezone = 'Europe/Dublin';
SET
regression=# select now() - '100 years'::interval;
              ?column?
-------------------------------------
 1907-01-06 03:28:22.483631-00:25:21
(1 row)


            regards, tom lane