Re: 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(at)ejurka(dot)com]
> Sent: Friday, January 05, 2007 2:52 PM
> To: Do, Leon (Leon)
> Cc: Oliver Jowett; Dave Cramer; imad; pgsql-jdbc(at)postgresql(dot)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
>
>