postgresql - SQLGrammarException when using Hibernate formula with Postgres specific function -

postgresql - SQLGrammarException when using Hibernate formula with Postgres specific function -

i have xml hibernate (3.6.10) mapping 1 line should fill property duration (type long) via formula. column starttime , endtime timestamp. hbm.xml file:

<property name="duration" formula="extract(epoch from(endtime - starttime))" />

when run within weblogic server, next sql created this:

select this_.extract(this_.epoch from(this_.endtime - this_.starttime)) formula4_0_ mytable this_;

this results in next exception:

nested exception org.hibernate.exception.sqlgrammarexception: not execute query

why hibernate not recognizing (postgres) functions epoch , extract? why adding table name? there way enforce it?

the hibernate dialect set "org.hibernate.dialect.postgresqldialect".

thx

edit: workaround or maybe improve solution implement getter method duration on java entity side. subtraction won't become performance bottleneck on application server. ;) appears more reliable respect various db dialects.

nevertheless interesting how original problem solved hibernate.

epoch isn't function, it's more keyword. though oddly doesn't appear on list of keywords, unreserved.

extract isn't postgresql-specific. it's standard sql. that's why has kind of insane syntax sql commission come instead of beingness written sane normal function extract('epoch', endtime - starttime).

however, epoch unit-specifier is postgresql extension sql-standard extract.

anyway, try:

extract('epoch' (endtime - starttime))

postgresql lets write first term or without quotes (i.e. keyword/identifier or literal). framework may understand improve if it's phrased literal.

otherwise, perhaps you'll have more luck non-standard to_char functoin:

select to_char(endtime - starttime, 'j');

as that's ordinary function. returns string you'll have convert integer, though, may want:

select cast( to_char(endtime - starttime, 'j') int4 )

... if orm doesn't mangle too.

compatibility of extract

mysql supports extract, not epoch unit-specifier.

[oracle supports extract(http://docs.oracle.com/cd/b19306_01/server.102/b14200/functions050.htm), not epoch unit-specifier.

ms-sql doesn't appear back upwards extract , wants utilize datepart instead.

hibernate postgresql