I found a failure in a JDBC driver of 7.2b4.
  (1) It does not support timestamptz type
  (2) Exception occurs by timestamp without time zone type
I attach a patch correcting the first failure.
You can confirm it in example.psql as follows:

---------------------------------------------------------------------
$ java -cp postgresql-examples.jar:postgresql.jar example.psql jdbc:postgresql:r-matuda r-matuda pass
PostgreSQL psql example v6.3 rev 1

Connecting to Database URL = jdbc:postgresql:r-matuda
Connected to PostgreSQL 7.2b4

[1] select 'now'::timestamp;
timestamptz
No class found for timestamptz.
[1] [1] select 'now'::timestamp with time zone;
timestamptz
No class found for timestamptz.
[1] [1] select 'now'::timestamp without time zone;
timestamp
Exception caught.
java.lang.StringIndexOutOfBoundsException: String index out of range: 26
java.lang.StringIndexOutOfBoundsException: String index out of range: 26
        at java.lang.String.charAt(String.java:516)
        at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653)
        at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398)
        at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:768)
        at example.psql.displayResult(psql.java:137)
        at example.psql.processLine(psql.java:96)
        at example.psql.<init>(psql.java:62)
        at example.psql.main(psql.java:227)
Tue, 25 Dec 2001 17:19:39 +0900
Ryouichi Matsuda <r-matuda@sra.co.jp>
Failure in timestamptz of JDBC of 7.2b4
Ryouichi,

I did not follow your explaination of the problem and therefore I don't 
see how your suggested patch fixes the problem.

You claim that the jdbc driver does not support the timestamptz type. 
However when I try using this type, I don't have any problems.  I just 
executed the sql statements you had included in your email:

select 'now'::timestamp;
select 'now'::timestamptz;
select 'now'::timestamp with time zone;

These all seem to work correctly for me.

Then however I did try your last query:

select 'now'::timestamp without time zone;

and this does fail for me with the string index out of bounds exception. 
  However the patch you sent does not seem to fix this error.  And I 
really don't know what to do with this datatype, since jdbc does not 
have a corresponding datatype that doesn't contain timezone information.

So to summarize, after looking at the sql statements you mention in your 
email, they all seem to work correctly for me without your patch being 
applied, except for the last one, which still fails even with your patch 
applied.  So I am unsure what your patch is supposed to fix, since I do 
not see any evidence that it fixes anything that is broken.

thanks,
--Barry



Ryouichi Matsuda wrote:

> I found a failure in a JDBC driver of 7.2b4.
>   (1) It does not support timestamptz type
>   (2) Exception occurs by timestamp without time zone type
> I attach a patch correcting the first failure.
> You can confirm it in example.psql as follows:
> 
> ---------------------------------------------------------------------
> $ java -cp postgresql-examples.jar:postgresql.jar example.psql jdbc:postgresql:r-matuda r-matuda pass
> PostgreSQL psql example v6.3 rev 1
> 
> Connecting to Database URL = jdbc:postgresql:r-matuda
> Connected to PostgreSQL 7.2b4
> 
> [1] select 'now'::timestamp;
> timestamptz
> No class found for timestamptz.
> [1] [1] select 'now'::timestamp with time zone;
> timestamptz
> No class found for timestamptz.
> [1] [1] select 'now'::timestamp without time zone;
> timestamp
> Exception caught.
> java.lang.StringIndexOutOfBoundsException: String index out of range: 26
> java.lang.StringIndexOutOfBoundsException: String index out of range: 26
>         at java.lang.String.charAt(String.java:516)
>         at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653)
>         at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398)
>         at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:768)
>         at example.psql.displayResult(psql.java:137)
>         at example.psql.processLine(psql.java:96)
>         at example.psql.<init>(psql.java:62)
>         at example.psql.main(psql.java:227)
> 
> 
> ------------------------------------------------------------------------
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 



Wed, 26 Dec 2001 18:44:35 -0800
Barry Lind <barry@xythos.com>
Re: [HACKERS] Failure in timestamptz of JDBC of 7.2b4

Barry Lind wrote:

> select 'now'::timestamp;
> select 'now'::timestamptz;
> select 'now'::timestamp with time zone;
>
> These all seem to work correctly for me.

When I executed SQL in <<example.psql>>, exception occurred.
In an approach by a getTimestamp() method, exception does not occur.

    Statement st = db.createStatement();
    ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
    rs.next();
    Timestamp ts = rs.getTimestamp(1);
    System.out.println(ts);


But, in an approach by a getObject() method, exception occurs.

    Statement st = db.createStatement();
    ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
    rs.next();
    Timestamp ts = (Timestamp)rs.getObject(1);
    System.out.println(ts);

Because a displayResult() method of 'example/psql.java' uses
getObject(), exception of 'No class found for timestamptz' occurs.
The patch which I attached to a former mail corrects this error.


> Then however I did try your last query:
> 
> select 'now'::timestamp without time zone;
> 
> and this does fail for me with the string index out of bounds exception. 
>   However the patch you sent does not seem to fix this error.  And I 
> really don't know what to do with this datatype, since jdbc does not 
> have a corresponding datatype that doesn't contain timezone information.

My patch does not correct this error. It is difficult for me to
correct this error justly, but I try to think.


In addition, I found an error of time type.

    Statement st = db.createStatement();
    ResultSet rs = st.executeQuery("SELECT 'now'::time");
    rs.next();
    Time t = rs.getTime(1);
    System.out.println(t);

This becomes string index out of bounds exception.


Mon, 07 Jan 2002 18:19:44 +0900
Ryouichi Matsuda <r-matuda@sra.co.jp>
Re: Failure in timestamptz of JDBC of 7.2b4
Ryouichi,

Thanks for the additional information.  I will look at this when I get 
some free time.

thanks,
--Barry



Ryouichi Matsuda wrote:

> Barry Lind wrote:
> 
> 
>>select 'now'::timestamp;
>>select 'now'::timestamptz;
>>select 'now'::timestamp with time zone;
>>
>>These all seem to work correctly for me.
>>
> 
> When I executed SQL in <<example.psql>>, exception occurred.
> In an approach by a getTimestamp() method, exception does not occur.
> 
>     Statement st = db.createStatement();
>     ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
>     rs.next();
>     Timestamp ts = rs.getTimestamp(1);
>     System.out.println(ts);
> 
> 
> But, in an approach by a getObject() method, exception occurs.
> 
>     Statement st = db.createStatement();
>     ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
>     rs.next();
>     Timestamp ts = (Timestamp)rs.getObject(1);
>     System.out.println(ts);
> 
> Because a displayResult() method of 'example/psql.java' uses
> getObject(), exception of 'No class found for timestamptz' occurs.
> The patch which I attached to a former mail corrects this error.
> 
> 
> 
>>Then however I did try your last query:
>>
>>select 'now'::timestamp without time zone;
>>
>>and this does fail for me with the string index out of bounds exception. 
>>  However the patch you sent does not seem to fix this error.  And I 
>>really don't know what to do with this datatype, since jdbc does not 
>>have a corresponding datatype that doesn't contain timezone information.
>>
> 
> My patch does not correct this error. It is difficult for me to
> correct this error justly, but I try to think.
> 
> 
> In addition, I found an error of time type.
> 
>     Statement st = db.createStatement();
>     ResultSet rs = st.executeQuery("SELECT 'now'::time");
>     rs.next();
>     Time t = rs.getTime(1);
>     System.out.println(t);
> 
> This becomes string index out of bounds exception.
> 
> 



Tue, 08 Jan 2002 10:19:27 -0800
Barry Lind <barry@xythos.com>
Re: Failure in timestamptz of JDBC of 7.2b4
Ryouichi,

I have applied the patch for this bug.  The fix won't be in 7.2b5, but 
will be in 7.2RC1.  It will also be in the latest builds on the 
jdbc.postgresql.org website.

thanks,
--Barry


Ryouichi Matsuda wrote:

> Barry Lind wrote:
> 
> 
>>select 'now'::timestamp;
>>select 'now'::timestamptz;
>>select 'now'::timestamp with time zone;
>>
>>These all seem to work correctly for me.
>>
> 
> When I executed SQL in <<example.psql>>, exception occurred.
> In an approach by a getTimestamp() method, exception does not occur.
> 
>     Statement st = db.createStatement();
>     ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
>     rs.next();
>     Timestamp ts = rs.getTimestamp(1);
>     System.out.println(ts);
> 
> 
> But, in an approach by a getObject() method, exception occurs.
> 
>     Statement st = db.createStatement();
>     ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
>     rs.next();
>     Timestamp ts = (Timestamp)rs.getObject(1);
>     System.out.println(ts);
> 
> Because a displayResult() method of 'example/psql.java' uses
> getObject(), exception of 'No class found for timestamptz' occurs.
> The patch which I attached to a former mail corrects this error.
> 
> 
> 
>>Then however I did try your last query:
>>
>>select 'now'::timestamp without time zone;
>>
>>and this does fail for me with the string index out of bounds exception. 
>>  However the patch you sent does not seem to fix this error.  And I 
>>really don't know what to do with this datatype, since jdbc does not 
>>have a corresponding datatype that doesn't contain timezone information.
>>
> 
> My patch does not correct this error. It is difficult for me to
> correct this error justly, but I try to think.
> 
> 
> In addition, I found an error of time type.
> 
>     Statement st = db.createStatement();
>     ResultSet rs = st.executeQuery("SELECT 'now'::time");
>     rs.next();
>     Time t = rs.getTime(1);
>     System.out.println(t);
> 
> This becomes string index out of bounds exception.
> 
> 



Mon, 14 Jan 2002 22:55:12 -0800
Barry Lind <barry@xythos.com>
Re: Failure in timestamptz of JDBC of 7.2b4
I wrote:
> In addition, I found an error of time type.
> 
>     Statement st = db.createStatement();
>     ResultSet rs = st.executeQuery("SELECT 'now'::time");
>     rs.next();
>     Time t = rs.getTime(1);
>     System.out.println(t);
> 
> This becomes string index out of bounds exception.

An attached patch corrects this error.

But time is always local time zone.
In this patch, time zone does not look.
Tue, 15 Jan 2002 19:41:31 +0900
Ryouichi Matsuda <r-matuda@sra.co.jp>
Re: Failure in timestamptz of JDBC of 7.2b4
Barry Lind wrote:
> Then however I did try your last query:
> 
> select 'now'::timestamp without time zone;
> 
> and this does fail for me with the string index out of bounds exception. 

An attached patch corrects problem of this bug and fractional second.


The handling of time zone was as follows:

  (a) with time zone
      using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
  (b) without time zone
      using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss")


About problem of fractional second,
Fractional second was changed from milli-second to nano-second.
Thu, 17 Jan 2002 20:00:01 +0900
Ryouichi Matsuda <r-matuda@sra.co.jp>
Re: Problem in ResultSet#getTimestamp() of 7.2b4
Hi Matsuda-san,

You beat me too it :) The following is a similar patch to the same code.
I've tested it with your GetTimestampTest.java code and it looks good
from what I can see. I'm attaching both jdbc1 and jdbc2 patches. This
patch changes a bit less in the code and basically adds a check to the
fraction loop for the end of string, as well as a check for a tz before
adding the GMT bit.

Tom.

On Thu, Jan 17, 2002 at 08:00:01PM +0900, Ryouichi Matsuda wrote:
> Barry Lind wrote:
> > Then however I did try your last query:
> > 
> > select 'now'::timestamp without time zone;
> > 
> > and this does fail for me with the string index out of bounds exception. 
> 
> An attached patch corrects problem of this bug and fractional second.
> 
> 
> The handling of time zone was as follows:
> 
>   (a) with time zone
>       using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
>   (b) without time zone
>       using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
> 
> 
> About problem of fractional second,
> Fractional second was changed from milli-second to nano-second.

-- 
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
Thu, 17 Jan 2002 22:57:08 +0900
"Thomas O'Dowd" <tom@nooper.com>
Re: Problem in ResultSet#getTimestamp() of 7.2b4

This has been saved for the 7.3 release:

	http://candle.pha.pa.us/cgi-bin/pgpatches2

---------------------------------------------------------------------------

Ryouichi Matsuda wrote:
> Barry Lind wrote:
> > Then however I did try your last query:
> > 
> > select 'now'::timestamp without time zone;
> > 
> > and this does fail for me with the string index out of bounds exception. 
> 
> An attached patch corrects problem of this bug and fractional second.
> 
> 
> The handling of time zone was as follows:
> 
>   (a) with time zone
>       using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
>   (b) without time zone
>       using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
> 
> 
> About problem of fractional second,
> Fractional second was changed from milli-second to nano-second.

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 24 Jan 2002 21:08:19 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem in ResultSet#getTimestamp() of 7.2b4

This has been saved for the 7.3 release:

	http://candle.pha.pa.us/cgi-bin/pgpatches2

---------------------------------------------------------------------------

Thomas O'Dowd wrote:
> Hi Matsuda-san,
> 
> You beat me too it :) The following is a similar patch to the same code.
> I've tested it with your GetTimestampTest.java code and it looks good
> from what I can see. I'm attaching both jdbc1 and jdbc2 patches. This
> patch changes a bit less in the code and basically adds a check to the
> fraction loop for the end of string, as well as a check for a tz before
> adding the GMT bit.
> 
> Tom.
> 
> On Thu, Jan 17, 2002 at 08:00:01PM +0900, Ryouichi Matsuda wrote:
> > Barry Lind wrote:
> > > Then however I did try your last query:
> > > 
> > > select 'now'::timestamp without time zone;
> > > 
> > > and this does fail for me with the string index out of bounds exception. 
> > 
> > An attached patch corrects problem of this bug and fractional second.
> > 
> > 
> > The handling of time zone was as follows:
> > 
> >   (a) with time zone
> >       using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
> >   (b) without time zone
> >       using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
> > 
> > 
> > About problem of fractional second,
> > Fractional second was changed from milli-second to nano-second.
> 
> -- 
> Thomas O'Dowd. - Nooping - http://nooper.com
> tom@nooper.com - Testing - http://nooper.co.jp/labs

[ Attachment, skipping... ]

[ Attachment, skipping... ]

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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 24 Jan 2002 21:08:28 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem in ResultSet#getTimestamp() of 7.2b4
Hi

I am trying to insert non english characters ( chars which lie in the
128-255 range in the ASCII character set ). When I try to get this data from
the table, I am getting garbled data. Is there any way to insert and
retrieve non english characters in pgsql

regards
Sulakshana


Fri, 25 Jan 2002 18:55:37 +0530
"Sulakshana Awsarikar" <sulakshana@mithi.com>
Inserting Non English characters in a varchar type field
I think, you must adjust the Characterset of your database. By default it is
ASCII, but you can create databases with unicode or one of the europien
character sets (LATIN-1 or something else)

That should solve your problem.

Oliver Friedrich


-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Sulakshana
Awsarikar
Sent: Friday, January 25, 2002 2:26 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Inserting Non English characters in a varchar type field


Hi

I am trying to insert non english characters ( chars which lie in the
128-255 range in the ASCII character set ). When I try to get this data from
the table, I am getting garbled data. Is there any way to insert and
retrieve non english characters in pgsql

regards
Sulakshana


---------------------------(end of broadcast)---------------------------
TIP 3: 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


Fri, 25 Jan 2002 15:18:49 +0100
"Oliver Friedrich" <oliver@familie-friedrich.de>
Re: Inserting Non English characters in a varchar type field
hi,
I get the following error while retrieving a datetime column using 
getTimestamp.

Bad Timestamp Format at 12 in 1970-1-1 0:0;


Bad Timestamp Format at 12 in 1970-1-1 0:0
        at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
        at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
        at 
org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)

regards,
Nagarajan.

Sat, 26 Jan 2002 14:33:15 +0100
Gunaseelan Nagarajan <gnagarajan@dkf.de>
Bad Timestamp Format
Hi Nagarajan,

What driver version are you using? The latest drivers at least
should switch the database to return ISO datestyle timestamps
when it first gets a connection. The current driver code expects
to parse the ISO format which is why you are getting this exception
below. Are you changing the datestyle in your program? I'm unfamilar
with how to get postgres to return a timestamp in the short format without
the seconds which you have below. The ISO format usually returns...
1970-01-01 00:00:00 if without time zone is specified. Can you perhaps
provide sample code that reproduces the problem using the latest driver?

You can download the latest drivers here...

	http://jdbc.postgresql.org/download.html

Cheers,

Tom.

On Sat, Jan 26, 2002 at 02:33:15PM +0100, Gunaseelan Nagarajan wrote:
> hi,
> I get the following error while retrieving a datetime column using 
> getTimestamp.
> 
> Bad Timestamp Format at 12 in 1970-1-1 0:0;
> 
> 
> Bad Timestamp Format at 12 in 1970-1-1 0:0
>         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
>         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
>         at 
> org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)
> 
> regards,
> Nagarajan.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs

Sun, 27 Jan 2002 00:47:24 +0900
"Thomas O'Dowd" <tom@nooper.com>
Re: Bad Timestamp Format
Hi Tom,

I went to ResultSet.java and made all the formats into
df = new SimpleDateFormat("yyyy-MM-dd HH:mm");

so now it works. As I don't require the seconds and milliseconds part,
it is ok now.

my program inserts the date values in the "yyyy-MM-dd HH:mm" format.
Perhaps that is causing the problem. However I am not changing the
default format either in the driver or in the database.

Thanks,
Nagarajan.


-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Thomas O'Dowd
Sent: Saturday, January 26, 2002 4:47 PM
To: Gunaseelan Nagarajan
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Bad Timestamp Format


Hi Nagarajan,

What driver version are you using? The latest drivers at least
should switch the database to return ISO datestyle timestamps
when it first gets a connection. The current driver code expects
to parse the ISO format which is why you are getting this exception
below. Are you changing the datestyle in your program? I'm unfamilar
with how to get postgres to return a timestamp in the short format without
the seconds which you have below. The ISO format usually returns...
1970-01-01 00:00:00 if without time zone is specified. Can you perhaps
provide sample code that reproduces the problem using the latest driver?

You can download the latest drivers here...

	http://jdbc.postgresql.org/download.html

Cheers,

Tom.

On Sat, Jan 26, 2002 at 02:33:15PM +0100, Gunaseelan Nagarajan wrote:
> hi,
> I get the following error while retrieving a datetime column using
> getTimestamp.
>
> Bad Timestamp Format at 12 in 1970-1-1 0:0;
>
>
> Bad Timestamp Format at 12 in 1970-1-1 0:0
>         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
>         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
>         at
> org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)
>
> regards,
> Nagarajan.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs

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


Sat, 26 Jan 2002 17:14:31 +0100
"G.Nagarajan" <gnagarajan@dkf.de>
Re: Bad Timestamp Format
Hi Nagarajan,

On Sat, Jan 26, 2002 at 05:14:31PM +0100, G.Nagarajan wrote:
> Hi Tom,
> 
> I went to ResultSet.java and made all the formats into
> df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
> 
> so now it works. As I don't require the seconds and milliseconds part,
> it is ok now.

Hmmmmmm. Not sure that this is the correct solution for your particular
problem (ie. altering the driver) although it may work for you now. It
would be better to find out the core of the problem and fix that.

> my program inserts the date values in the "yyyy-MM-dd HH:mm" format.
> Perhaps that is causing the problem. However I am not changing the
> default format either in the driver or in the database.

What database version are you using? What driver version are you using?
I can't figure out how to get 7.2 beta to return a shorter timestamp
then 1970-01-01 00:00:00. even if I insert a short timestamp. I presume
you are using older code? As I said before, the driver should force
the datestyle to ISO when it first gets a connection.

select '1970-1-1 0:0'::timestamp without time zone, '1970-1-1 0:0'::timestamp;
      timestamp      |      timestamptz       
---------------------+------------------------
 1970-01-01 00:00:00 | 1970-01-01 00:00:00+09
(1 row)

If I can figure out how you are doing it and if the its a problem in the
latest code, then we can fix it.

Tom.

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Thomas O'Dowd
> Sent: Saturday, January 26, 2002 4:47 PM
> To: Gunaseelan Nagarajan
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Bad Timestamp Format
> 
> 
> Hi Nagarajan,
> 
> What driver version are you using? The latest drivers at least
> should switch the database to return ISO datestyle timestamps
> when it first gets a connection. The current driver code expects
> to parse the ISO format which is why you are getting this exception
> below. Are you changing the datestyle in your program? I'm unfamilar
> with how to get postgres to return a timestamp in the short format without
> the seconds which you have below. The ISO format usually returns...
> 1970-01-01 00:00:00 if without time zone is specified. Can you perhaps
> provide sample code that reproduces the problem using the latest driver?
> 
> You can download the latest drivers here...
> 
> 	http://jdbc.postgresql.org/download.html
> 
> Cheers,
> 
> Tom.
> 
> On Sat, Jan 26, 2002 at 02:33:15PM +0100, Gunaseelan Nagarajan wrote:
> > hi,
> > I get the following error while retrieving a datetime column using
> > getTimestamp.
> >
> > Bad Timestamp Format at 12 in 1970-1-1 0:0;
> >
> >
> > Bad Timestamp Format at 12 in 1970-1-1 0:0
> >         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
> >         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
> >         at
> > org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)
> >
> > regards,
> > Nagarajan.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> 
> --
> Thomas O'Dowd. - Nooping - http://nooper.com
> tom@nooper.com - Testing - http://nooper.co.jp/labs
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs

Sun, 27 Jan 2002 14:50:13 +0900
"Thomas O'Dowd" <tom@nooper.com>
Re: Bad Timestamp Format
Hi Tom,

I am using PostgreSQL 7.1.3. It was built from the source file
by using ./configure --with-java etc. I used the jdbc source that comes
along with the source and did not download it seperately.

Here is my java environment.
	JDK 1.3.10
	JBoss 2.4

I use the following code for inserting the date value

	public String giveInsert(Calendar cal)
		throws Exception{
	    String colValue = "";
	    colValue =  "'" + cal.get(Calendar.YEAR) + "-"
		    + ( cal.get( Calendar.MONTH ) + 1 ) + "-"
		    + cal.get(Calendar.DAY_OF_MONTH) + " "
		    + cal.get( Calendar.HOUR_OF_DAY ) + ":"
		    + cal.get( Calendar.MINUTE ) + "'";

		return colValue;
	}

For retreiving from the database, I use the following code

	public Calendar takeFromResultSet(ResultSet rs, String columnName)
		throws SQLException, Exception{
 	Timestamp ts = rs.getTimestamp(columnName);
	Calendar c = Calendar.getInstance();
	if(ts != null)
			c.set(ts.getYear()+1900, ts.getMonth(), ts.getDate(), 
ts.getHours(),ts.getMinutes());
		else
			c = null;
		return c;
	}

The database connection comes through the JBoss connection pool handler. 
Maybe that is not setting the required database property?

here is the configuration settings in jboss.jcml

<mbean code="org.jboss.jdbc.XADataSourceLoader" 
name="DefaultDomain:service=XADataSource,name=postgresqlDS">
	<attribute 
name="DataSourceClass">org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl</attribute>
	<attribute name="PoolName">postgresqlDS</attribute>
	<attribute name="URL">jdbc:postgresql:db</attribute>
	<attribute name="JDBCUser">root</attribute>
	<attribute name="Password">japan</attribute>
</mbean>


To get the database connection, I use the following code

		cat.debug("Using jboss pool to get Connection");
		Context ctx = new InitialContext();
		javax.sql.DataSource ds =(javax.sql.DataSource)ctx.lookup( jndiName );
		con = ds.getConnection(); 

Thanks and Regards,
Nagarajan.
	
On Saturday 26 January 2002 14:33, you wrote:
> hi,
> I get the following error while retrieving a datetime column using
> getTimestamp.
>
> Bad Timestamp Format at 12 in 1970-1-1 0:0;
>
>
> Bad Timestamp Format at 12 in 1970-1-1 0:0
>         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
>         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
>         at
> org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)
>
> regards,
> Nagarajan.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Sun, 27 Jan 2002 15:22:58 +0100
Gunaseelan Nagarajan <gnagarajan@dkf.de>
Re: Bad Timestamp Format
Hi Nagarajan,

I'm not familiar with jboss, but I just tried messing with an old
7.1.2 database that I had lying around and can't get it to return
a short format of a timestamp like "1970-1-1 0:0". The driver
code in 7.1.3 will cause an exception for this short form as you
are getting and the 7.2 driver will just parse the datepart but
not the hour/minute part. 

Before changing the new driver code to handle this format, I'd
like to know how you are generating it, so that a) I know it makes
sense to support it and b) that I can test it. Can anyone on the
list let me know how to get the backend to return this short format?

The only way I can think about doing it is using the to_char() 
functions which you would have to be explicitly doing??? ie, something
like:

select now(), to_char(now(), 'YYYY-FMMM-FMDD FMHH24:FMMI');
          now           |     to_char     
------------------------+-----------------
 2002-01-28 10:26:09+09 | 2002-1-28 10:26
(1 row)

If your select code or jboss is somehow using to_char() to alter the
default timestamp string, its doing something unusual which the driver
doesn't support.

Can anyone add anymore reasons why the backend would be returning 
a short timestamp string to the driver, such as specific datestyle
options, a specific locale or other?

Tom.

On Sun, Jan 27, 2002 at 03:22:58PM +0100, Gunaseelan Nagarajan wrote:
> Hi Tom,
> 
> I am using PostgreSQL 7.1.3. It was built from the source file
> by using ./configure --with-java etc. I used the jdbc source that comes
> along with the source and did not download it seperately.
> 
> Here is my java environment.
> 	JDK 1.3.10
> 	JBoss 2.4
> 
> I use the following code for inserting the date value
> 
> 	public String giveInsert(Calendar cal)
> 		throws Exception{
> 	    String colValue = "";
> 	    colValue =  "'" + cal.get(Calendar.YEAR) + "-"
> 		    + ( cal.get( Calendar.MONTH ) + 1 ) + "-"
> 		    + cal.get(Calendar.DAY_OF_MONTH) + " "
> 		    + cal.get( Calendar.HOUR_OF_DAY ) + ":"
> 		    + cal.get( Calendar.MINUTE ) + "'";
> 
> 		return colValue;
> 	}
> 
> For retreiving from the database, I use the following code
> 
> 	public Calendar takeFromResultSet(ResultSet rs, String columnName)
> 		throws SQLException, Exception{
>  	Timestamp ts = rs.getTimestamp(columnName);
> 	Calendar c = Calendar.getInstance();
> 	if(ts != null)
> 			c.set(ts.getYear()+1900, ts.getMonth(), ts.getDate(), 
> ts.getHours(),ts.getMinutes());
> 		else
> 			c = null;
> 		return c;
> 	}
> 
> The database connection comes through the JBoss connection pool handler. 
> Maybe that is not setting the required database property?
> 
> here is the configuration settings in jboss.jcml
> 
> <mbean code="org.jboss.jdbc.XADataSourceLoader" 
> name="DefaultDomain:service=XADataSource,name=postgresqlDS">
> 	<attribute 
> name="DataSourceClass">org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl</attribute>
> 	<attribute name="PoolName">postgresqlDS</attribute>
> 	<attribute name="URL">jdbc:postgresql:db</attribute>
> 	<attribute name="JDBCUser">root</attribute>
> 	<attribute name="Password">japan</attribute>
> </mbean>
> 
> 
> To get the database connection, I use the following code
> 
> 		cat.debug("Using jboss pool to get Connection");
> 		Context ctx = new InitialContext();
> 		javax.sql.DataSource ds =(javax.sql.DataSource)ctx.lookup( jndiName );
> 		con = ds.getConnection(); 
> 
> Thanks and Regards,
> Nagarajan.
> 	
> On Saturday 26 January 2002 14:33, you wrote:
> > hi,
> > I get the following error while retrieving a datetime column using
> > getTimestamp.
> >
> > Bad Timestamp Format at 12 in 1970-1-1 0:0;
> >
> >
> > Bad Timestamp Format at 12 in 1970-1-1 0:0
> >         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
> >         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
> >         at
> > org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)
> >
> > regards,
> > Nagarajan.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org

-- 
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs

Mon, 28 Jan 2002 10:29:39 +0900
"Thomas O'Dowd" <tom@nooper.com>
Re: Bad Timestamp Format
Hi Tom,

I use a "Select * from table" and no to_char() functions. I will post
the same question to the jboss-mailing list and see if anyone there
has had the same problem. It could also be a feature of the JBoss
resultset implementation.

Regards,
Nagarajan.

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Thomas O'Dowd
Sent: Monday, January 28, 2002 2:30 AM
To: Gunaseelan Nagarajan
Cc:
Subject: Re: [JDBC] Bad Timestamp Format


Hi Nagarajan,

I'm not familiar with jboss, but I just tried messing with an old
7.1.2 database that I had lying around and can't get it to return
a short format of a timestamp like "1970-1-1 0:0". The driver
code in 7.1.3 will cause an exception for this short form as you
are getting and the 7.2 driver will just parse the datepart but
not the hour/minute part.

Before changing the new driver code to handle this format, I'd
like to know how you are generating it, so that a) I know it makes
sense to support it and b) that I can test it. Can anyone on the
list let me know how to get the backend to return this short format?

The only way I can think about doing it is using the to_char()
functions which you would have to be explicitly doing??? ie, something
like:

select now(), to_char(now(), 'YYYY-FMMM-FMDD FMHH24:FMMI');
          now           |     to_char
------------------------+-----------------
 2002-01-28 10:26:09+09 | 2002-1-28 10:26
(1 row)

If your select code or jboss is somehow using to_char() to alter the
default timestamp string, its doing something unusual which the driver
doesn't support.

Can anyone add anymore reasons why the backend would be returning
a short timestamp string to the driver, such as specific datestyle
options, a specific locale or other?

Tom.

On Sun, Jan 27, 2002 at 03:22:58PM +0100, Gunaseelan Nagarajan wrote:
> Hi Tom,
>
> I am using PostgreSQL 7.1.3. It was built from the source file
> by using ./configure --with-java etc. I used the jdbc source that comes
> along with the source and did not download it seperately.
>
> Here is my java environment.
> 	JDK 1.3.10
> 	JBoss 2.4
>
> I use the following code for inserting the date value
>
> 	public String giveInsert(Calendar cal)
> 		throws Exception{
> 	    String colValue = "";
> 	    colValue =  "'" + cal.get(Calendar.YEAR) + "-"
> 		    + ( cal.get( Calendar.MONTH ) + 1 ) + "-"
> 		    + cal.get(Calendar.DAY_OF_MONTH) + " "
> 		    + cal.get( Calendar.HOUR_OF_DAY ) + ":"
> 		    + cal.get( Calendar.MINUTE ) + "'";
>
> 		return colValue;
> 	}
>
> For retreiving from the database, I use the following code
>
> 	public Calendar takeFromResultSet(ResultSet rs, String columnName)
> 		throws SQLException, Exception{
>  	Timestamp ts = rs.getTimestamp(columnName);
> 	Calendar c = Calendar.getInstance();
> 	if(ts != null)
> 			c.set(ts.getYear()+1900, ts.getMonth(), ts.getDate(),
> ts.getHours(),ts.getMinutes());
> 		else
> 			c = null;
> 		return c;
> 	}
>
> The database connection comes through the JBoss connection pool handler.
> Maybe that is not setting the required database property?
>
> here is the configuration settings in jboss.jcml
>
> <mbean code="org.jboss.jdbc.XADataSourceLoader"
> name="DefaultDomain:service=XADataSource,name=postgresqlDS">
> 	<attribute
>
name="DataSourceClass">org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl</attr
ibute>
> 	<attribute name="PoolName">postgresqlDS</attribute>
> 	<attribute name="URL">jdbc:postgresql:db</attribute>
> 	<attribute name="JDBCUser">root</attribute>
> 	<attribute name="Password">japan</attribute>
> </mbean>
>
>
> To get the database connection, I use the following code
>
> 		cat.debug("Using jboss pool to get Connection");
> 		Context ctx = new InitialContext();
> 		javax.sql.DataSource ds =(javax.sql.DataSource)ctx.lookup( jndiName );
> 		con = ds.getConnection();
>
> Thanks and Regards,
> Nagarajan.
>
> On Saturday 26 January 2002 14:33, you wrote:
> > hi,
> > I get the following error while retrieving a datetime column using
> > getTimestamp.
> >
> > Bad Timestamp Format at 12 in 1970-1-1 0:0;
> >
> >
> > Bad Timestamp Format at 12 in 1970-1-1 0:0
> >         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
> >         at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
> >         at
> >
org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)
> >
> > regards,
> > Nagarajan.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs

---------------------------(end of broadcast)---------------------------
TIP 3: 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


Mon, 28 Jan 2002 09:33:10 +0100
"G.Nagarajan" <gnagarajan@dkf.de>
Re: Bad Timestamp Format

Your patch has been added to the PostgreSQL unapplied patches list at:

	http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Thomas O'Dowd wrote:
> Hi Matsuda-san,
> 
> You beat me too it :) The following is a similar patch to the same code.
> I've tested it with your GetTimestampTest.java code and it looks good
> from what I can see. I'm attaching both jdbc1 and jdbc2 patches. This
> patch changes a bit less in the code and basically adds a check to the
> fraction loop for the end of string, as well as a check for a tz before
> adding the GMT bit.
> 
> Tom.
> 
> On Thu, Jan 17, 2002 at 08:00:01PM +0900, Ryouichi Matsuda wrote:
> > Barry Lind wrote:
> > > Then however I did try your last query:
> > > 
> > > select 'now'::timestamp without time zone;
> > > 
> > > and this does fail for me with the string index out of bounds exception. 
> > 
> > An attached patch corrects problem of this bug and fractional second.
> > 
> > 
> > The handling of time zone was as follows:
> > 
> >   (a) with time zone
> >       using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
> >   (b) without time zone
> >       using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
> > 
> > 
> > About problem of fractional second,
> > Fractional second was changed from milli-second to nano-second.
> 
> -- 
> Thomas O'Dowd. - Nooping - http://nooper.com
> tom@nooper.com - Testing - http://nooper.co.jp/labs

[ Attachment, skipping... ]

[ Attachment, skipping... ]

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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 22 Feb 2002 21:09:50 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem in ResultSet#getTimestamp() of 7.2b4

Patch applied.  Thanks.

---------------------------------------------------------------------------


Thomas O'Dowd wrote:
> Hi Matsuda-san,
> 
> You beat me too it :) The following is a similar patch to the same code.
> I've tested it with your GetTimestampTest.java code and it looks good
> from what I can see. I'm attaching both jdbc1 and jdbc2 patches. This
> patch changes a bit less in the code and basically adds a check to the
> fraction loop for the end of string, as well as a check for a tz before
> adding the GMT bit.
> 
> Tom.
> 
> On Thu, Jan 17, 2002 at 08:00:01PM +0900, Ryouichi Matsuda wrote:
> > Barry Lind wrote:
> > > Then however I did try your last query:
> > > 
> > > select 'now'::timestamp without time zone;
> > > 
> > > and this does fail for me with the string index out of bounds exception. 
> > 
> > An attached patch corrects problem of this bug and fractional second.
> > 
> > 
> > The handling of time zone was as follows:
> > 
> >   (a) with time zone
> >       using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
> >   (b) without time zone
> >       using  SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
> > 
> > 
> > About problem of fractional second,
> > Fractional second was changed from milli-second to nano-second.
> 
> -- 
> Thomas O'Dowd. - Nooping - http://nooper.com
> tom@nooper.com - Testing - http://nooper.co.jp/labs

[ Attachment, skipping... ]

[ Attachment, skipping... ]

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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 4 Mar 2002 23:11:03 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem in ResultSet#getTimestamp() of 7.2b4