0

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.

Community
  • 1
  • 1
Alexey Romanov
  • 167,066
  • 35
  • 309
  • 487

2 Answers2

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.

mightyrick
  • 910
  • 4
  • 6
  • PostgreSQL doesn't support computed columns, AFAIK (http://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql). Having it actually stored in the database 1) breaks normalization; 2) requires a lot of storage (this table might end up with billions of rows in the future, and actually has 2 timestamps, so growing each row by 16 bytes is a bad idea). Replacing offset column with timestamp + offset would be a bit better, actually. Using a view may also be a good idea. – Alexey Romanov Jan 10 '13 at 22:24
0

What I've done in similar situations before is to declare the setters/getters private or protected and then have public @Transient getters to perform the desired calculation.

This solves all programmatic issues. For desired jpa sql you'll need to perform the appropriate calculation in the query.

Craig Taylor
  • 1,689
  • 1
  • 11
  • 13