SQLGrammarException when using Hibernate formula with Postgres specific function

Question

I have an XML Hibernate (3.6.10) mapping with one line which should fill the property duration (type is long) via a formula. The column startTime and endTime is timestamp. From my hbm.xml file:

<property name="duration" formula="extract(epoch from(endTime - startTime))" />

When I run this within a WebLogic server, the following sql is created for this:

select this_.extract(this_.epoch from(this_.endTime - this_.startTime)) 
  as formula4_0_ from MYTABLE this_;

This results in the following exception:

 nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

Why is hibernate not recognizing the (postgres) functions epoch and extract? Why is it adding the table name? Is there a way to enforce it?

The hibernate dialect is set to "org.hibernate.dialect.PostgreSQLDialect".

Thx

EDIT: As a workaround or maybe better solution I will implement the getter method for duration on the java entity side. Hopefully the subtraction won't become a performance bottleneck on the application server. ;) This appears to be more reliable with respect to various db dialects.

Nevertheless it would be interesting how the original problem could be solved with hibernate.

Answer 1

epoch isn't a function, it's more like a keyword. Though oddly it doesn't appear on the list of keywords, even as unreserved.

extract isn't PostgreSQL-specific. It's standard SQL. That's why it has the kind of insane syntax only the SQL committee could come up with instead of being written as a sane normal function like extract('epoch', endtime - starttime).

However, the epoch unit-specifier is a PostgreSQL extension to the SQL-standard extract.

Anyway, try:

extract('epoch' FROM (endTime - startTime))

PostgreSQL lets you write the first term with or without quotes (i.e. as a keyword/identifier or as a literal). Your framework may understand it better if it's phrased as a literal.

Otherwise, perhaps you'll have more luck with the non-standard to_char function:

SELECT to_char(endTime - startTime, 'J');

as that's just an ordinary function. It returns a string you'll have to convert to an integer, though, so you may want:

SELECT CAST( to_char(endTime - startTime, 'J') AS int4 )

... if your ORM doesn't mangle that too.

---

Compatibility of extract

• MySQL supports EXTRACT, but not the epoch unit-specifier.

• [Oracle supports EXTRACT(http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm), but not the epoch unit-specifier.

• MS-SQL doesn't appear to support EXTRACT and wants you to use DATEPART instead.