Re: Bad Timestamp Format

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)

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
>

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.

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

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

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.

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.

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(at)nooper(dot)com - Testing - http://nooper.co.jp/labs

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at)nooper(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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

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(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Sulakshana
Awsarikar
Sent: Friday, January 25, 2002 2:26 PM
To: pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

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.

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(at)nooper(dot)com - Testing - http://nooper.co.jp/labs

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(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Thomas O'Dowd
Sent: Saturday, January 26, 2002 4:47 PM
To: Gunaseelan Nagarajan
Cc: pgsql-jdbc(at)postgresql(dot)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(at)nooper(dot)com - Testing - http://nooper.co.jp/labs

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

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(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Thomas O'Dowd
> Sent: Saturday, January 26, 2002 4:47 PM
> To: Gunaseelan Nagarajan
> Cc: pgsql-jdbc(at)postgresql(dot)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(at)nooper(dot)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(at)nooper(dot)com - Testing - http://nooper.co.jp/labs

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

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(at)nooper(dot)com - Testing - http://nooper.co.jp/labs

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(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)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(at)nooper(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

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(at)nooper(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at)nooper(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026