Oracle table data comparison through time

Another flashback tips. This one allows you to compare data from your tables through time. Keep in mind the flashback setting of your oracle instance defines how far back in time you can go.

The key here is to perform an inner join between the table and its flashback counterpart. Here's how :

select current.value, past.valuefrom my_table current

inner join my_table as of timestamp sysdate-1 past
on current.id=past.id
where current.blah > 0

This query compares table my_table between its current state and its state one day ago (sysdate-1). Of course, one can use as of timestamp to_timestamp(....) to set a more specific time. Inner join is performed on the id field (if there's one :) ), and i added a filter (the where part), but this is for the show. The core of the functionality is the inner join w/ as of timestamp part.