JPA and Temporal Database Support

[jgrassel]

A number of databases support temporal data management[1] (that is, the ability to "go back in time" and query the state of the database as it was in the past), such as DB/2 [2], Oracle [3], Postgresql [4], etc. As of JPA 2.2, this is a capability that has not been addressed by the specification.

I'm opening this issue as a means to begin a conversation about the subject, whether it has merit to add to the spec, and to identify the challenges adding such a capability to the JPA spec itself.

Useful Links: [1] https://en.wikipedia.org/wiki/Temporal_database [2] https://www.ibm.com/developerworks/data/library/techarticle/dm-1204db2temporaldata/ [3] https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html [4] https://pgxn.org/dist/temporal_tables/

[rbygrave]

Note the Ebean ORM has support for AS OF and VERSIONS BETWEEN and includes support for this on MySql and Postgres via triggers and history tables.

Refer: https://ebean.io/docs/features/history

The support for this feature included:

• query.asOf(timestamp)
• query.findVersionsBetween(timestamp0, timestamp1)
• Version - a holder for results of a versions between query with start timestamp, end timestamp, properties diff and the version of the bean (that was effective for that period)
• @History - a marker on entity beans that have temporal support
• @HistoryExclude - to exclude properties and relationships from temporal support
• For MySql and Postgres it includes DDL generation for history tables and triggers

[m-reza-rahman]

This has been open a while and I don't see much traction. Honestly I also don't know how well supported or often used this feature is either. Maybe this should be closed to reduce clutter or marked as very low priority so the focus can be on higher impact work?

Reza Rahman Jakarta EE Ambassador, Author, Blogger, Speaker

Please note views expressed here are my own as an individual community member and do not reflect the views of my employer.

[beikov]

IMO this would be a great addition and I think that the model EBean provides is interesting. I don't think that the mapping of start/end system time should be forced onto users though. It's obvious that the history entity needs this information, but the main entity shouldn't require it. I don't know if the temporal database support has a notion of "revisions" like Hibernate Envers, but I think that is something a possible extension to JPA should optionally support.

[gavinking]

So this is something I would be prepared to work on and write up a proposal for, iff:

• there's a consensus among current JPA implementers that this is something they would actually like to implement, and if
• folks familiar with existing implementations would help me out by sending a short description of what are the semantics of it, as seen by the Java program.

To clarify, it seems to me that what this would be a definition of (API+semantics) for how a Java program using JPA interacts with temporal data. I don't think you could/should/would want to specify how it maps to the underlying relational model. In particular, I don't think it should be defined to depend on built-in features of the current generation of RDBMs (nor should it be defined to not take advantage of them).

Note also that I guess it could in principle be defined as an "optional add-on" to JPA, rather than as a required feature. (Though that's an issue that would need to be properly discussed/thought-through.)

On the other hand, if there's no other JPA implementors which are keen on supporting this feature, then there's really no point in adding it to JPA.