Is there an SQL database that can rollback in time?

views:

answers:

+5 Q:

Is there an SQL database that can rollback in time?

I am looking for an SQL database of some type that is entirely based around the concept of time, just like those that are geo-centric, relationship-centric, etc.

My main wish is that I can make changes to the database as I go along, and then at any point run a query that essentially says, show me how the database looked at this date and time. I would prefer the SQL database have this functionality built in, instead of having to re-implement it using a log table and recording each and every transaction.

Am I hoping for a pipe dream?

Thanks!

EDIT: After some further research, it seems that a "temporal database" might be what I'm looking for. So any information on using one of those, if there are any good open-source ones available, etc., would be very helpful!

+6 A:

Oracle has Flashback. Closest I can think of on SQL Server requires snapshots, without resorting to restoring from transaction logs (requires full recovery model).

OMG Ponies 2010-07-22 21:33:00

I'm looking for something that treats this more like a "feature", not a recovery tool.After reading about temporal DB's, that seems to be the ticket. You can basically query the database and say "as of right now, what do you think about X", but you can also say "as of Y time, what did you think about X?", even if the values would (in a typical DB) have been overwritten by then.

Riley Dutton 2010-07-22 21:39:10

@Riley Dutton: Oracle's Flashback is the closest I know of then. What you ask for is typically transaction log domain...

OMG Ponies 2010-07-22 21:48:22

That's exactly how Oracle's flashback works. SELECT * FROM my_tableAS OF TIMESTAMP '2010-01-01' (or a similar syntax)You can even set the whole transaction to a point in the past, and then all queries will return values from back then.To go a really long way back, you'll need Oracle 11g and it's "Flashback Archive" which is quite costly though.

a_horse_with_no_name 2010-07-22 21:49:28

Okay, thanks for the information. I think Oracle is probably a non-starter, since it costs money, but maybe reading about it will give me some good ideas.

Riley Dutton 2010-07-22 22:04:29

@Riley Dutton: Take a look over at otn.oracle.com. You can get the database for free for development purposes, not sure about Flashback. Unless they've changed their policies over the past few years, you aren't time limited on the development usage, just once it goes into production, you need to properly license it.

ManiacZX 2010-07-22 23:36:52

@RileyDutton, @ManiacZX, @a_horse_with_no_name : Oracle's Total Recall (aka Flashback Archive) option - which is the temporal database implementation - is a chargeable extra on the Enterprise Edition, so it *is* expensive. However, the markup isn't as gouging as some of Oracle's other chargeable extras and the feature does work really neatly.

APC 2010-07-24 14:38:17

+1 A:

You may want to check out TimeDB.

Note, though, that you can do the exact same thing yourself by implementing a ValidTimeBegin and ValidTimeEnd columns in your tables and populating them accordingly. The tool simply takes its special SQL statements and converts them to SQL92-compliant statements.

sheepsimulator 2010-07-22 21:37:53

What your looking for software historian. These are databases where all data is organised temporally, really their core function. Once of the uses of the software historian, is to enable a playback function, where time series data can be played back in the order it was entered, which is often critical in those industries that require it, like real time applications in finance, gas, oil and electric utilities.

Some of the companies that offer software historians, are

Wonderware FactoryTalk OSI PI

scope_creep 2010-07-22 22:38:00

ansaurus

tags:

views:

answers:

Is there an SQL database that can rollback in time?

related questions