Re: Storing and retreiving infinity timestamp values from

[Kris Jurka <books(at)ejurka(dot)com>]

On Wed, 27 Sep 2006, Mario Splivalo wrote:

> I have read some posts on this mailinglist concerning the infinity value
> of the timestamp data type, where it was stated that an exception is
> thrown when one attempts to read infinity value. Now, on the postgresql
> jdbc there is an entry in TODO list wich mentions adding handling of
> 'infinity' values to the jdbc documentation.
>
> Is it possible for someone to post some code here for reading/storing
> infinity timestamp values to and from postgres database?

See attached.  We use special constants to indicate infinity because Java 
has no equivalent.  Looking at this test output, the displayed value of 
our negative infinity value looks a little suspect and I suspect there's 
something timezone dependent going on there.  Any change to that shouldn't 
affect your code, just the constants value.

Kris Jurka

import java.sql.*;

import org.postgresql.PGStatement;

public class InfTS {
	
	public static void main(String args[]) throws Exception {
		Class.forName("org.postgresql.Driver");
		Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/jurka","jurka","");

		Statement stmt = conn.createStatement();
		stmt.execute("CREATE TEMP TABLE infts (a timestamptz)");

		PreparedStatement pstmt = conn.prepareStatement("INSERT INTO infts VALUES (?)");
		pstmt.setTimestamp(1, new Timestamp(PGStatement.DATE_POSITIVE_INFINITY));
		pstmt.executeUpdate();
		pstmt.setTimestamp(1, new Timestamp(PGStatement.DATE_NEGATIVE_INFINITY));
		pstmt.executeUpdate();

		ResultSet rs = stmt.executeQuery("SELECT * FROM infts");
		while (rs.next()) {
			System.out.println(rs.getString(1));
			System.out.println("Is +inf: " + (rs.getTimestamp(1).getTime() == PGStatement.DATE_POSITIVE_INFINITY));
			System.out.println("Is -inf: " + (rs.getTimestamp(1).getTime() == PGStatement.DATE_NEGATIVE_INFINITY));
			System.out.println(rs.getTimestamp(1));
		}

	}
}