Hibernate postgresql comparing date

Question

I'm using Hibernate and PostgreSQL 8.4 database in a Java application. I have the following query:

Query q = session.createQuery("from User where validStartDate < "  + getDate() +" and validEndDate >" + getDate());

where validStartDate is a Date in a PostgreSQL database and getDate returns a String from the current date by using the SimpleDateFormat.

But I keep getting an error:

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: date < integer
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
        at org.hibernate.loader.Loader.doQuery(Loader.java:674)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
        at org.hibernate.loader.Loader.doList(Loader.java:2220)
        ... 49 more
2010-11-29 20:42:19 org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 42883
2010-11-29 20:42:19 org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ERROR: operator does not exist: date < integer

How do I cast the value so it is of proper type Date rather than integer?

Thanks for help.

user330315 · Accepted Answer · 2010-11-29 20:12:45Z

5

If getDate() returns a String then you'll need to put it into single quotes. You are sending something like this:

SELECT ...
FROM ...
WHERE validStartDate < 2010-11-29

which is read as "where the column validStartDate is lower than the result of calculating the result of 2010 minus 11 minus 29"

Now if you put 2010-11-29 into single quotes PostgreSQL will cast that String to a date but whether that will succeed depends on the formatting you apply in the SimpleDateFormatter

Edit: if you want to make sure the date literal is always treated correctly regardles of any client side locale settings, use a proper ANSI date literal:

SELECT ...
FROM ...
WHERE validStartDate < DATE '2010-11-29'

where the actual literal between the quotes has to be provided in the ISO formatted as shown above. Note the keyword DATE which specifies the ANSI date literal

answered Nov 29, 2010 at 20:12

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

sass Over a year ago

Thanks a lot. I use yyyy-MM-dd.

user330315 Over a year ago

Using the DATE '2010-11-29' syntax will still be more robust and - as you are actuall providing a date - might be better from a performance point of view. Because no implicit casting takes place that might void the usage of any index

matt b · Accepted Answer · 2010-11-29 21:40:35Z

1

I'd suggest using parameters in your query and let the postgres driver handling converting from Strings to the appropriate types/escaping for you - this is why parameterized queries exist.

answered Nov 29, 2010 at 21:40

matt b

140k66 gold badges287 silver badges352 bronze badges

Collectives™ on Stack Overflow

Hibernate postgresql comparing date

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related