Re: [INTERFACES] ResultSet.getDate

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