0

I would like to use the system versioning coming with version 2.5.0 but when I launch a request of the type:

SELECT firstname, lastname, email FROM customer FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP - 1 YEAR

It does not find any record when there are some which have just been inserted ...

psilocybe
  • 35
  • 6

2 Answers2

0

Your query means "return the rows that existed a year ago". Because you inserted the rows recently, or added system versioning recently, the query returns no rows.

fredt
  • 24,044
  • 3
  • 40
  • 61
0

As I have found some of the things I am looking to do with the system versioning coming with Hsqldb 2.5.x, I allow myself to post this answer.

To retrieve the previous version of a row, which is the last version before the current version, or more simply to find all the UPDATE made for 1 year, simply execute the query:

SELECT previous.* FROM customer AS current, customer FOR SYSTEM_TIME
FROM CURRENT_TIMESTAMP - 1 YEAR TO CURRENT_TIMESTAMP AS previous
WHERE current.customerid = ? AND previous.customerid = ? AND
current.start = previous.stop;

Assuming that:

    start: is the column declared as TIMESTAMP GENERATED ALWAYS AS ROW START.
    stop: is the column declared as TIMESTAMP GENERATED ALWAYS AS ROW END.
    customerid: is the primary key to the customer table, because I have to find the UPDATE for each customer.

I now have to find the INSERT and DELETE queries, I would come back for that.

PS: I didn't invent anything, it comes from BD2 documentation, so it may not be optimal, but it works with Hsqldb system versioning.

Edit: This answer is not satisfactory, see here for something much better

psilocybe
  • 35
  • 6