Обсуждение: ResultSet.getDate
Hi Peter! ;-) I think I may have found a problem in the postgres JDBC driver V6.4. (BTW I assume the one I build from the released source in postgresql-6.4.2.tar.gz is the same as the jdbc6.4.jar downloadable from your web site at http://www.retep.org.uk/postgres ? In anycase, the problem seems to occur with both.) Anyway I get an SQLException (well a parse exception, really), when attempting to use the ResultSet.getDate methods. The exception messages are (I omitted the portions of the stack trace through my code): Bad Date Format: at 0 in Fri Jan 29 04:29:50 1999 UTC at postgresql.ResultSet.getDate(ResultSet.java:408) Indeed, "Fri Jan 29 04:29:50 1999 UTC" is how dates are output from my postgres databases. I am useing Redhat 5.2 and postgres built from the above mentioned source file. The date format that is being selected for the date formatter (ResultSet.java, line #403) is "MM-dd-yyyy" Which is of course not what postgres sends. Also, in looking at this code, it seems that the time portion is being thrown away in ResultSet.getDate, I am not sure if this is what is intended from looking at the JDBC API documentation published by Sun. == Peace, Peter We are Microsoft of Borg, you will be assimilated!!! Resistance is fut... ***BZZZRT*** THUD!!! [General Protection Fault in MSBorg32.DLL] Please contact the vendor for more information _________________________________________________________ DO YOU YAHOO!? Get your free @yahoo.com address at http://mail.yahoo.com
Hi again! :-) Please accept my apologies, I forgot to mention that the TYPE of the field the table in question is "DATETIME"! > I think I may have found a problem in the postgres > JDBC driver V6.4. (BTW I assume the one I build from == Peace, Peter We are Microsoft of Borg, you will be assimilated!!! Resistance is fut... ***BZZZRT*** THUD!!! [General Protection Fault in MSBorg32.DLL] Please contact the vendor for more information _________________________________________________________ DO YOU YAHOO!? Get your free @yahoo.com address at http://mail.yahoo.com
On Sat, 30 Jan 1999, Peter Garner wrote:
> Bad Date Format: at 0 in Fri Jan 29 04:29:50 1999 UTC
> at postgresql.ResultSet.getDate(ResultSet.java:408)
>
I've seen almost the same thing, using v6.4.2. I use timestamps and not
datetime as you in your next mail said you did (wow, I can see the future
:)
For me the problem is in the SimpleDateFormat class that doesn't recognize
the timezone value returned by the database (CET?). This may be a java bug
in the linux port, a timezone setting problem, ... I made a simple
workaround, that doesn't depend on timezone names. I haven't had time to
test it properly yet, see below.
If I remember correctly removing LC_CTYPE also made the conversion work
(but that may have been for my testprogram only).
> Indeed, "Fri Jan 29 04:29:50 1999 UTC" is how dates are
> output from my postgres databases. I am useing Redhat
> 5.2 and postgres built from the above mentioned source
> file. The date format that is being selected for the
> date formatter (ResultSet.java, line #403) is
>
> "MM-dd-yyyy"
>
> Which is of course not what postgres sends. Also, in
> looking at this code, it seems that the time portion
> is being thrown away in ResultSet.getDate, I am not
> sure if this is what is intended from looking at the
> JDBC API documentation published by Sun.
>
In Timestamps date and time is returned. Here is my changed getTimestamp
that uses GMT+hh:mm for the timezone.
public Timestamp getTimestamp(int columnIndex) throws SQLException
{
String s = getString(columnIndex);
if(s == null)
return null; // SQL NULL
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzz");
int TZ = new Float(s.substring(19)).intValue();
//BUG: negative values?
String nm = "GMT+" + TZ +":00"; // use GMT+hh:mm format
s = s.substring(0,19) + nm;
try {
java.util.Date d = df.parse(s);
return new Timestamp(d.getTime());
} catch (ParseException e) {
e.printStackTrace();
throw new SQLException("Bad Timestamp Format: at " + e.getErrorOffset() + " in " + s);
}
}
It is obviously not correct for negative TZ. Also it uses the timezone of
the value returned by the database and I don't know if that is different
from the clients timezone. Therefore I haven't sent it anywhere before.
Before you convert to timestamp, you should know that min() and max() is
not implemented for timestamps (or?), so you'll need to create the proper
aggregate functions. (Ask and I'll send the ones I made)
/Urban
---
Urban Widmark urban@svenskatest.se
Svenska Test AB +46 90 71 71 23
On Sat, 30 Jan 1999, Peter Garner wrote: > Hi Peter! ;-) > > I think I may have found a problem in the postgres > JDBC driver V6.4. (BTW I assume the one I build from > the released source in postgresql-6.4.2.tar.gz is the > same as the jdbc6.4.jar downloadable from your web site > at http://www.retep.org.uk/postgres ? In anycase, the > problem seems to occur with both.) > > Anyway I get an SQLException (well a parse exception, > really), when attempting to use the ResultSet.getDate > methods. The exception messages are (I omitted the > portions of the stack trace through my code): > > Bad Date Format: at 0 in Fri Jan 29 04:29:50 1999 UTC > at postgresql.ResultSet.getDate(ResultSet.java:408) > > Indeed, "Fri Jan 29 04:29:50 1999 UTC" is how dates are > output from my postgres databases. I am useing Redhat > 5.2 and postgres built from the above mentioned source > file. The date format that is being selected for the > date formatter (ResultSet.java, line #403) is > > "MM-dd-yyyy" > > Which is of course not what postgres sends. Also, in > looking at this code, it seems that the time portion > is being thrown away in ResultSet.getDate, I am not > sure if this is what is intended from looking at the > JDBC API documentation published by Sun. I'll check, but what version of the JDK/JRE are you using? The java.util.Date class was broken in pre jdk1.1.6 VM's. I am thinking on re-working the date handling for PostgreSQL 6.5, as I'm still seeing problems with them. Peter -- Peter T Mount peter@retep.org.uk Main Homepage: http://www.retep.org.uk PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres Java PDF Generator: http://www.retep.org.uk/pdf
> I am thinking on re-working the date handling for PostgreSQL 6.5, as
> I'm still seeing problems with them.
Anything we should consider doing on the server side to help??
- Tom
On Mon, 1 Feb 1999, Thomas G. Lockhart wrote:
> > I am thinking on re-working the date handling for PostgreSQL 6.5, as
> > I'm still seeing problems with them.
>
> Anything we should consider doing on the server side to help??
Not sure. The first thing I'm thinking of, is removing the datestyle
support, and having the driver set a datestyle to one (probably ISO)
during the connect. AFAIK, the ODBC driver uses a similar scheme?
This should reduce the size of the driver a little, and remove the number
of places where this error could occur.
If we still have problems, how about an internal datestyle for use by
interfaces only (like JDBC & ODBC)?
Peter
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
> Not sure. The first thing I'm thinking of, is removing the datestyle
> support, and having the driver set a datestyle to one (probably ISO)
> during the connect. AFAIK, the ODBC driver uses a similar scheme?
Yes, sorry, I'd forgotten that you don't already do this. The ODBC
driver does force the date format to ISO.
> If we still have problems, how about an internal datestyle for use by
> interfaces only (like JDBC & ODBC)?
Sure, though the ISO style is pretty well defined and parsable, so may
be the best choice for an ASCII internal format.
- Tom
Hi Peter! ;-) >I'll check, but what version of the JDK/JRE are you >using? The java.util.Date class was broken in pre >jdk1.1.6 VM's. I am using the JDK 1.1.7v1a from Blackdown. Indeed, Java's handling of dates is a little funky. But in this case the problem is that the returned strings are not being handled properly. Calling getString instead of getDate returns : "Mon Feb 01 16:08:00 1999 UTC" And the format string used in the SimpleDateFormatter object that getDate uses to convert that string is not correct for that format. If there is anything I can do to help, please let me know! :-) == Peace, Peter We are Microsoft of Borg, you will be assimilated!!! Resistance is fut... ***BZZZRT*** THUD!!! [General Protection Fault in MSBorg32.DLL] Please contact the vendor for more information _________________________________________________________ DO YOU YAHOO!? Get your free @yahoo.com address at http://mail.yahoo.com
On Mon, 1 Feb 1999, Thomas G. Lockhart wrote:
> > Not sure. The first thing I'm thinking of, is removing the datestyle
> > support, and having the driver set a datestyle to one (probably ISO)
> > during the connect. AFAIK, the ODBC driver uses a similar scheme?
>
> Yes, sorry, I'd forgotten that you don't already do this. The ODBC
> driver does force the date format to ISO.
>
> > If we still have problems, how about an internal datestyle for use by
> > interfaces only (like JDBC & ODBC)?
>
> Sure, though the ISO style is pretty well defined and parsable, so may
> be the best choice for an ASCII internal format.
This is probably the way I will go.
Peter
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
Peter Garner <peter_garner@yahoo.com> writes:
> I am using the JDK 1.1.7v1a from Blackdown. Indeed,
> Java's handling of dates is a little funky. But in
> this case the problem is that the returned strings
> are not being handled properly. Calling getString
> instead of getDate returns :
>
> "Mon Feb 01 16:08:00 1999 UTC"
>
> And the format string used in the SimpleDateFormatter
> object that getDate uses to convert that string is
> not correct for that format. If there is anything I
> can do to help, please let me know! :-)
Perhaps opening your connections as follows might help you
...
// open connection
conn = DriverManager.getConnection (url, usr, pwd);
// set datestyle
Statement st = conn.createStatement ();
st.executeUpdate ("set datestyle='iso'");
st.executeUpdate ("show datestyle");
st.close ();
...
--
Aleksey Demakov
avd@gcom.ru
On 2 Feb 1999, Aleksey Demakov wrote:
> Peter Garner <peter_garner@yahoo.com> writes:
>
> > I am using the JDK 1.1.7v1a from Blackdown. Indeed,
> > Java's handling of dates is a little funky. But in
> > this case the problem is that the returned strings
> > are not being handled properly. Calling getString
> > instead of getDate returns :
> >
> > "Mon Feb 01 16:08:00 1999 UTC"
> >
> > And the format string used in the SimpleDateFormatter
> > object that getDate uses to convert that string is
> > not correct for that format. If there is anything I
> > can do to help, please let me know! :-)
>
> Perhaps opening your connections as follows might help you
>
> ...
> // open connection
> conn = DriverManager.getConnection (url, usr, pwd);
>
> // set datestyle
> Statement st = conn.createStatement ();
> st.executeUpdate ("set datestyle='iso'");
> st.executeUpdate ("show datestyle");
> st.close ();
I'm thinking on doing this within the driver, forcing it to use iso all
the time.
Peter
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf