What should the type of parameter be in Java when it is a "timestamp without time zone" in postgresql?

Question

I am trying to run a query in postgresql through Java and JDBC.

The "activitydate" column of "table" in postgresql is "timestamp without time zone". I tried to use String to pass the date but didn't work(org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone > character varying).

What type instead of String should I use to store "start_date" and "end_date" and pass them to the query?

BTW SELECT COUNT(*) FROM table WHERE activitydate > '2014-11-20' AND activitydate < '2014-11-21' AND zipcode = 12345 runs perfectly in pgadmin.

public Response query(
          @QueryParam("start_date") String start_date,
          @QueryParam("end_date") String end_date,
          @QueryParam("zipcode") String zipcode
          ) throws Exception {
...
...

pstmt = conn.prepareStatement("SELECT COUNT(*) FROM table WHERE activitydate > ? AND activitydate < ? AND zipcode = ?");

pstmt.setString(1, start_date); 
pstmt.setString(2, end_date); 
pstmt.setString(3, zipcode); 

Update: I changed it to java.sql.Timestamp but still doesn't work:

public Response query(
          @QueryParam("start_date") Timestamp start_date,
          @QueryParam("end_date") Timestamp end_date,
          @QueryParam("zipcode") String zipcode
          ) throws Exception {
...
...

pstmt = conn.prepareStatement("SELECT COUNT(*) FROM table WHERE activitydate > ? AND activitydate < ? AND zipcode = ?");

pstmt.setTimestamp(1, start_date); 
pstmt.setTimestamp(2, end_date); 
pstmt.setString(3, zipcode);

Answer 1

tl;dr

For SQL using Half-Open span-of-time:
"SELECT * FROM tbl WHERE when !< ? AND when < ? ; "

myPreparedStatement.setObject(       // Use a prepared statement so you can pass smart objects rather than dumb strings.
    1 ,                              // Specify which placeholder is being fulfilled.
    LocalDate                        // Represent a date-only value, without time-of-day and without time zone or offset-from-UTC.
    .parse( "2014-11-20" )           // Parse an input string in standard ISO 8601 format to get a `LocalDate` object.
    .atStartOfDay()                  // Determine the first moment of the day on that date. Returns a `LocalDateTime` object representing a date with time-of-day but lacking any concept of time zone or offset-from-UTC.
) ;
myPreparedStatement.setObject( 
    2 , 
    LocalDate
    .parse( "2014-11-21" )
    .atStartOfDay()
) ;

Beware: I am guessing you are using the wrong type for your column in your database. Moments can only be tracked with TIMESTAMP WITH TIME ZONE, not TIMESTAMP WITHOUT TIME ZONE. Search Stack Overflow for more info.

Not a moment

The Answer by Jens is now outmoded. Nowadays you should be using the modern java.time classes that supplanted the troublesome old legacy date-time classes.

“timestamp without time zone” in postgresql?

This data type on both Postgres and the SQL standard purposely lacks the context of an offset-from-UTC or a time zone. So, beware, this type cannot represent a moment, is not a point on the timeline.

I changed it to java.sql.Timestamp

Nope, wrong data type. Besides being legacy, and being terribly flawed in design, the java.sql.Timestamp class represents a moment, a specific point on the timeline. So this is a mismatch with your column of type TIMESTAMP WITHOUT TIME ZONE.

LocalDateTime

You should instead be using the LocalDateTime class. This class represents a date with a time-of-day but lacks any concept of offset or time zone.

To retrieve a value from the database.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

To send a value to the database.

myPreparedStatement.setObject( … , ldt ) ;

Start of day

SELECT COUNT(*) FROM table WHERE activitydate > '2014-11-20' AND activitydate < '2014-11-21'

You have another mismatch here. You are providing a date-only value but your column holds date-with-time-of-day values.

Another problem: You are using dumb strings where you should be using smart objects. As of JDBC 4.2 we can exchange java.time objects with the database. Use a PreparedStatement with placeholders, and pass objects via the `set

To solve the date with time-of-day problem, we need to determine the start of the day. With LocalDateTime, we have no time zone anomalies to account for. So the day always starts at 00:00. Nevertheless, we should get in the habit of asking java.time to determine the start-of-day.

LocalDate startDate = LocalDate.parse( "2014-11-20" ) ;
LocalDate stopDate = LocalDate.parse( "2014-11-21" ) ;

LocalDateTime start = startDate.atStartOfDay() ;
LocalDateTime stop = stopDate.atStartOfDay() ;

Write you SQL with placeholders.

I suggest you change your thinking to make a habit of the Half-Open approach to defining span-of-time. In Half-Open, the beginning is inclusive while the ending is exclusive. So for the single entire day of the 20th, search for dates equal to or later than the 20th, and running up to, but not including, the 21st. As for that first part, a shorter way to say "equal to or later" is "not before", so we use !<.

String sql = "SELECT * FROM tbl WHERE when !< ? AND when < ? ; " ;

Feed that sql string to your prepared statement. Then pass the two LocalDateTime objects for the placeholders.

myPreparedStatement.setObject( 1 , start ) ;
myPreparedStatement.setObject( 2 , stop ) ;

Answer 2

You have to use a java.sql.Timestamp or a java.sql.Date.