Converting datetime to a long timestamp in JPQL

Question

I have an entity with two columns:

// time when the event happened (stored in UTC)
@Temporal(TemporalType.TIMESTAMP)
private Date eventTime;

// timezone offset in milliseconds
// so local millis are eventTime.getTime() + offset
@Basic
private int offset;

It appears that in JPQL queries I can't use something like WHERE eventTime + offset > :parameter. But is it possible to work around by casting eventTime to a long in some way? Of course, there is an option of using native queries...

EDIT: It isn't supported in the standard and isn't mentioned in OpenJPA documentation, so probably not possible at the moment.

Answer 1

It may not be possible for you to modify the table, but can you possibly just add another column which is the computed timestamp with offset? Then just base all of your queries off of that? It will probably yield better performance, as well.

Another way I've solved problems like this is to create a SQL view and and create a different @Entity based on that view. I have done this when I need a lot of complex computations and don't want all of that complexity in the code itself. This is useful when you have other non-JPA based applications that want to get at the same information (such as a reporting engine). While this solution ends up using a SQL view, it allows your Java/JPA code to not have to deal with native queries.