pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
What JDBC datatype can be used for DATETIME ?
4+ messages / 4 participants
[nested] [flat]

* What JDBC datatype can be used for DATETIME ?
@ 1999-08-19 11:23 Jon Bundesen <jon@it-konsulenterne.dk>
  0 siblings, 0 replies; 4+ messages in thread

From: Jon Bundesen @ 1999-08-19 11:23 UTC (permalink / raw)
  To: pgsql-sql; jon@it-konsulenterne.dk

 I'm trying to read a DATETIME field from my Pg 6.4.2 using JDBC.

When i try reading the date from my ResultSet using ResultSet.getDate()
i get an error stating:

java.sql.SQLException: Bad Date Format: at 0 in Thu Aug 19 12:19:52 1999
CEST
 at postgresql.ResultSet.getDate(ResultSet.java:407)
 at TestDate.main(TestDate.java:32)

When i try using ResultSet.getTimestamp() instead, i also get an error:
java.lang.NumberFormatException:  1999 CEST
 at java.lang.Float.<init>(Float.java)
 at postgresql.ResultSet.getTimestamp(ResultSet.java:455)
 at TestDate.main(TestDate.java:32)

I CAN use getString(), but i guess the has to be a better way ?

Can anyone help ?

Best regards,
    Jon Bundesen.


Here is the code if anyone is interested
============================

Table    = foo
+----------------------------------+----------------------------------+-------+

|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+

| dt                               | datetime
|     8 |
+----------------------------------+----------------------------------+-------+



import java.sql.*;
import java.sql.Date;

public class TestDate {

  public static void main (String args[])
    throws Exception
  {
    Connection con;
    Statement statement;
    PreparedStatement preparedStatement;
    ResultSet rs;
    // String url = "jdbc:postgresql:test1";
    String url = "jdbc:postgresql://127.0.0.1:5432/test1";

    Class.forName("postgresql.Driver");
    con = DriverManager.getConnection(url, "jon", "");
    con.setAutoCommit( false );

    //

    statement = con.createStatement();
    statement.executeUpdate("insert into foo values ( current_timestamp
)");
    con.commit();
    statement.close();

    //

    statement = con.createStatement();
    rs = statement.executeQuery("SELECT * FROM foo");
    while (rs.next()) {
      System.out.println("Result: " + rs.getTimestamp(1));
    }
    rs.close();
    statement.close();

    //

    con.close();
  }
}




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* What JDBC datatype can be used for DATETIME ?
@ 1999-08-19 15:42 Jon Windfeld Bundesen <jon@it-konsulenterne.dk>
  1999-08-20 06:47 ` Re: [SQL] What JDBC datatype can be used for DATETIME ? Esteban Chiner Sanz <echiner@tissat.es>
  1999-08-22 12:22 ` Re: [SQL] What JDBC datatype can be used for DATETIME ? Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 2 replies; 4+ messages in thread

From: jon@it-konsulenterne.dk @ 1999-08-19 15:42 UTC (permalink / raw)
  To: pgsql-sql

 I'm trying to read a DATETIME field from my Pg 6.4.2 using JDBC.

When i try reading the date from my ResultSet using ResultSet.getDate()
i get an error stating:

java.sql.SQLException: Bad Date Format: at 0 in Thu Aug 19 12:19:52 1999

CEST
 at postgresql.ResultSet.getDate(ResultSet.java:407)
 at TestDate.main(TestDate.java:32)

When i try using ResultSet.getTimestamp() instead, i also get an error:
java.lang.NumberFormatException:  1999 CEST
 at java.lang.Float.<init>(Float.java)
 at postgresql.ResultSet.getTimestamp(ResultSet.java:455)
 at TestDate.main(TestDate.java:32)

I CAN use getString(), but i'd like to get a real Date object, not just
a String. Can anyone help ?

Best regards,
    Jon Bundesen.


Here is the code if anyone is interested:
===========================

Table = foo
+----------------------------------+----------------------------------+-------+

|              Field                                |
Type                               | Length|
+----------------------------------+----------------------------------+-------+

| dt                                                 |
datetime                                       |     8     |
+----------------------------------+----------------------------------+-------+

import java.sql.*;
import java.sql.Date;

public class TestDate {

  public static void main (String args[])
    throws Exception
  {
    Connection con;
    Statement statement;
    PreparedStatement preparedStatement;
    ResultSet rs;
    // String url = "jdbc:postgresql:test1";
    String url = "jdbc:postgresql://127.0.0.1:5432/test1";

    Class.forName("postgresql.Driver");
    con = DriverManager.getConnection(url, "jon", "");
    con.setAutoCommit( false );

    //

    statement = con.createStatement();
    statement.executeUpdate("insert into foo values ( current_timestamp
)");
    con.commit();
    statement.close();

    //

    statement = con.createStatement();
    rs = statement.executeQuery("SELECT * FROM foo");
    while (rs.next()) {
      System.out.println("Result: " + rs.getTimestamp(1));
      //System.out.println("Result: " + rs.getDate(1));
    }
    rs.close();
    statement.close();

    //

    con.close();
  }
}






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] What JDBC datatype can be used for DATETIME ?
  1999-08-19 15:42 What JDBC datatype can be used for DATETIME ? Jon Windfeld Bundesen <jon@it-konsulenterne.dk>
@ 1999-08-20 06:47 ` Esteban Chiner Sanz <echiner@tissat.es>
  1 sibling, 0 replies; 4+ messages in thread

From: Esteban Chiner Sanz @ 1999-08-20 06:47 UTC (permalink / raw)
  To: Jon Windfeld Bundesen <jon@it-konsulenterne.dk>; +Cc: pgsql-sql

I had the same problem and I finally ended up using the timestamp type in postgres
and using the getTimestamp function y Java. That worked fine for me.

                Esteban Chiner

Jon Windfeld Bundesen escribió:

>  I'm trying to read a DATETIME field from my Pg 6.4.2 using JDBC.
>
> When i try reading the date from my ResultSet using ResultSet.getDate()
> i get an error stating:
>
> java.sql.SQLException: Bad Date Format: at 0 in Thu Aug 19 12:19:52 1999
>
> CEST
>  at postgresql.ResultSet.getDate(ResultSet.java:407)
>  at TestDate.main(TestDate.java:32)
>
> When i try using ResultSet.getTimestamp() instead, i also get an error:
> java.lang.NumberFormatException:  1999 CEST
>  at java.lang.Float.<init>(Float.java)
>  at postgresql.ResultSet.getTimestamp(ResultSet.java:455)
>  at TestDate.main(TestDate.java:32)
>
> I CAN use getString(), but i'd like to get a real Date object, not just
> a String. Can anyone help ?
>
> Best regards,
>     Jon Bundesen.
>
> Here is the code if anyone is interested:
> ===========================
>
> Table = foo
> +----------------------------------+----------------------------------+-------+
>
> |              Field                                |
> Type                               | Length|
> +----------------------------------+----------------------------------+-------+
>
> | dt                                                 |
> datetime                                       |     8     |
> +----------------------------------+----------------------------------+-------+
>
> import java.sql.*;
> import java.sql.Date;
>
> public class TestDate {
>
>   public static void main (String args[])
>     throws Exception
>   {
>     Connection con;
>     Statement statement;
>     PreparedStatement preparedStatement;
>     ResultSet rs;
>     // String url = "jdbc:postgresql:test1";
>     String url = "jdbc:postgresql://127.0.0.1:5432/test1";
>
>     Class.forName("postgresql.Driver");
>     con = DriverManager.getConnection(url, "jon", "");
>     con.setAutoCommit( false );
>
>     //
>
>     statement = con.createStatement();
>     statement.executeUpdate("insert into foo values ( current_timestamp
> )");
>     con.commit();
>     statement.close();
>
>     //
>
>     statement = con.createStatement();
>     rs = statement.executeQuery("SELECT * FROM foo");
>     while (rs.next()) {
>       System.out.println("Result: " + rs.getTimestamp(1));
>       //System.out.println("Result: " + rs.getDate(1));
>     }
>     rs.close();
>     statement.close();
>
>     //
>
>     con.close();
>   }
> }
>
> ************

------------------------------------------------------------
 Esteban Chiner Sanz        mailto (work): echiner@tissat.es
 TISSAT
 Av. Aragon, 30, 5ª planta  Phone: 96 393 9950
 Valencia (SPAIN)





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] What JDBC datatype can be used for DATETIME ?
  1999-08-19 15:42 What JDBC datatype can be used for DATETIME ? Jon Windfeld Bundesen <jon@it-konsulenterne.dk>
@ 1999-08-22 12:22 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 0 replies; 4+ messages in thread

From: Herouth Maoz @ 1999-08-22 12:22 UTC (permalink / raw)
  To: jon@it-konsulenterne.dk; pgsql-sql

At 18:42 +0300 on 19/08/1999, Jon Windfeld Bundesen wrote:


>
> When i try using ResultSet.getTimestamp() instead, i also get an error:
> java.lang.NumberFormatException:  1999 CEST
>  at java.lang.Float.<init>(Float.java)
>  at postgresql.ResultSet.getTimestamp(ResultSet.java:455)
>  at TestDate.main(TestDate.java:32)
>
> I CAN use getString(), but i'd like to get a real Date object, not just
> a String. Can anyone help ?

Basically, Java recognises the string structure sent to it by PostgreSQL's
timestamp. Since the string structure sent by datetime is different by
default, it fails to interpret is as a datetime:

testing=> SELECT '22-aug-1999 15:10'::timestamp;
?column?
----------------------
1999-08-22 15:10:00+03
(1 row)

testing=> SELECT '22-aug-1999 15:10'::datetime;
?column?
----------------------------
Sun Aug 22 15:10:00 1999 IDT
(1 row)

The former is the ISO style. One may change the datestyle to get the same
result out of datetime, but this is not recommended within the context of
JDBC, because the JDBC interpretation functions set it themselves, if I'm
not mistaken:

testing=> SET DATESTYLE='iso';
SET VARIABLE
testing=> SELECT '22-aug-1999 15:10'::datetime;
?column?
----------------------
1999-08-22 15:10:00+03
(1 row)

Another solution that achieves the same goal is to make sure all the fields
you select from your tables are in timestamp rather than datetime. Either
have that in your table (but this has a problem with indexing), or just
change your selects. Instead of

SELECT dt FROM foo;

Use

SELECT timestamp( dt ) FROM foo;

In 6.4 there is a bug in this, so you can define your own function for the
conversion. Or you can use abstime(dt) instead. The conversion function
would be:

testing=> CREATE FUNCTION dt_timestamp( datetime ) RETURNS timestamp
testing-> AS 'SELECT timestamp_in( datetime_out( $1 ) )
testing'>     WHERE $1 IS NOT NULL'
testing-> LANGUAGE 'sql';

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1999-08-22 12:22 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-19 11:23 What JDBC datatype can be used for DATETIME ? Jon Bundesen <jon@it-konsulenterne.dk>
1999-08-19 15:42 What JDBC datatype can be used for DATETIME ? Jon Windfeld Bundesen <jon@it-konsulenterne.dk>
1999-08-20 06:47 ` Esteban Chiner Sanz <echiner@tissat.es>
1999-08-22 12:22 ` Herouth Maoz <herouth@oumail.openu.ac.il>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox