[ACCEPTED]-Why do we need a temporal database?-temporal-database

Consider your appointment/journal diary 51 - it goes from Jan 1st to Dec 31st. Now 50 we can query the diary for appointments/journal 49 entries on any day. This ordering is called 48 the valid time. However, appointments/entries are 47 not usually inserted in order.

Suppose I 46 would like to know what appointments/entries 45 were in my diary on April 4th. That is, all 44 the records that existed in my diary on 43 April 4th. This is the transaction time.

Given that appointments/entries 42 can be created and deleted etc. A typical 41 record has a beginning and end valid time 40 that covers the period of the entry and 39 a beginning and end transaction time that 38 indicates the period during which the entry 37 appeared in the diary.

This arrangement is 36 necessary when the diary may undergo historical revision. Suppose 35 on April 5th I realise that the appointment 34 I had on Feb 14th actually occurred on February 33 12th i.e. I discover an error in my diary 32 - I can correct the error so that the valid 31 time picture is corrected, but now, my query 30 of what was in the diary on April 4th would 29 be wrong, UNLESS, the transaction times 28 for appointments/entries are also stored. In 27 that case if I query my diary as of April 26 4th it will show an appointment existed 25 on February 14th but if I query as of April 24 6th it would show an appointment on February 23 12th.

This time travel feature of a temporal 22 database makes it possible to record information 21 about how errors are corrected in a database. This 20 is necessary for a true audit picture of 19 data that records when revisions were made 18 and allows queries relating to how data 17 have been revised over time.

Most business 16 information should be stored in this bitemporal 15 scheme in order to provide a true audit 14 record and to maximise business intelligence 13 - hence the need for support in a relational 12 database. Notice that each data item occupies 11 a (possibly unbounded) square in the two 10 dimensional time model which is why people 9 often use a GIST index to implement bitemporal 8 indexing. The problem here is that a GIST 7 index is really designed for geographic 6 data and the requirements for temporal data 5 are somewhat different.

PostgreSQL 9.0 exclusion 4 constraints should provide new ways of organising 3 temporal data e.g. transaction and valid 2 time PERIODs should not overlap for the 1 same tuple.

A temporal database efficiently stores a 9 time series of data, typically by having 8 some fixed timescale (such as seconds or 7 even milliseconds) and then storing only 6 changes in the measured data. A timestamp 5 in an RDBMS is a discretely stored value 4 for each measurement, which is very inefficient. A 3 temporal database is often used in real-time 2 monitoring applications like SCADA. A well-established 1 system is the PI database from OSISoft (http://www.osisoft.com/).

As I understand it (and over-simplifying 9 enormously), a temporal database records 8 facts about when the data was valid as well 7 as the the data itself, and permits you 6 to query on the temporal aspects. You end 5 up dealing with 'valid time' and 'transaction 4 time' tables, or 'bitemporal tables' involving 3 both 'valid time' and 'transaction time' aspects. You 2 should consider reading either of these 1 two books:

• Darwen, Date and Lorentzos "Temporal Data and the Relational Model" (out of print),
• and (at a radically different extreme) "Developing Time-Oriented Database Applications in SQL", Richard T. Snodgrass, Morgan Kaufmann Publishers, Inc., San Francisco, July, 1999, 504+xxiii pages, ISBN 1-55860-436-7. That is out of print but available as PDF on his web site at cs.arizona.edu (so a Google search makes it pretty easy to find).

Temporal databases are often used in the 5 financial services industry. One reason 4 is that you are rarely (if ever) allowed 3 to delete any data, so ValidFrom - ValidTo 2 type fields on records are used to provide 1 an indication of when a record was correct.

Besides "what new things can I do with 33 it", it might be useful to consider 32 "what old things does it unify?". The 31 temporal database represents a particular 30 generalization of the "normal" SQL 29 database. As such, it may give you a unified 28 solution to problems that previously appeared 27 unrelated. For example:

• Web Concurrency When your database has a web UI that lets multiple users perform standard Create/Update/Delete (CRUD) modifications, you have to face the concurrent web changes problem. Basically, you need to check that an incoming data modification is not affecting any records that have changed since that user last saw those records. But if you have a temporal database, it quite possibly already associates something like a "revision ID" with each record (due to the difficulty of making timestamps unique and monotonically ascending). If so, then that becomes the natural, "already built-in" mechanism for preventing the clobbering of other users' data during database updates.
• Legal/Tax Records The legal system (including taxes) places rather more emphasis on historical data than most programmers do. Thus, you will often find advice about schemas for invoices and such that warns you to beware of deleting records or normalizing in a natural way--which can lead to an inability to answer basic legal questions like "Forget their current address, what address did you mail this invoice to in 2001?" With a temporal framework base, all the machinations to those problems (they usually are halfway steps to having a temporal database) go away. You just use the most natural schema, and delete when it make sense, knowing that you can always go back and answer historical questions accurately.

On the other hand, the 26 temporal model itself is half-way to complete 25 revision control, which could inspire further 24 applications. For example, suppose you roll 23 your own temporal facility on top of SQL 22 and allow branching, as in revision control 21 systems. Even limited branching could make 20 it easy to offer "sandboxing" -- the 19 ability to play with and modify the database 18 with abandon without causing any visible 17 changes to other users. That makes it easy 16 to supply highly realistic user training 15 on a complex database.

Simple branching with 14 a simple merge facility could also simplify 13 some common workflow problems. For example, a 12 non-profit might have volunteers or low-paid 11 workers doing data entry. Giving each worker 10 their own branch could make it easy to allow 9 a supervisor to review their work or enhance 8 it (e.g., de-duplification) before merging 7 it into the main branch where it would become 6 visible to "normal" users. Branches 5 could also simplify permissions. If a user 4 is only granted permission to use/see their 3 unique branch, you don't have to worry about 2 preventing every possible unwanted modification; you'll 1 only merge the changes that make sense anyway.

Apart from reading the Wikipedia article? A database that 5 maintains an "audit log" or similar 4 transaction log will have some properties 3 of being "temporal". If you need 2 answers to questions about who did what to whom and when then you've 1 got a good candidate for a temporal database.

You can imagine a simple temporal database 13 that just logs your GPS location every few 12 seconds. The opportunities for compressing 11 this data is great, a normal database you 10 would need to store a timestamp for every 9 row. If you have a great deal of throughput 8 required, knowing the data is temporal and 7 that updates and deletes to a row will never 6 be required permits the program to drop 5 a lot of the complexity inherit in a typical 4 RDBMS.

Despite this, temporal data is usually 3 just stored in a normal RDBMS. PostgreSQL, for 2 example has some temporal extensions, which makes this a little 1 easier.

Two reasons come to mind:

1. Some are optimized for insert and read only and can offer dramatic perf improvements
2. Some have better understandings of time than traditional SQL - allowing for grouping operations by second, minute, hour, etc

0

Just an update, Temporal database is coming 10 to SQL Server 2016.

To clear all your doubts 9 why one need a Temporal Database, rather 8 than configuring with custom methods, and 7 how efficiently & seamlessly SQL Server 6 configures it for you, check the in-depth 5 video and demo on Channel9.msdn here: https://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016

MSDN 4 link: https://msdn.microsoft.com/en-us/library/dn935015(v=sql.130).aspx

Currently with the CTP2 (beta 2) release 3 of SQL Server 2016 you can play with it.

Check 2 this video on how to use Temporal Tables in SQL Server 1 2016.

My understanding of temporal databases is 16 that are geared towards storing certain 15 types of temporal information. You could 14 simulate that with a standard RDBMS, but 13 by using a database that supports it you 12 have built-in idioms for a lot of concepts 11 and the query language might be optimized 10 for these sort of queries.

To me this is 9 a little like working with a GIS-specific 8 database rather than an RDBMS. While you 7 could shove coordinates in a run-of-the-mill 6 RDBMS, having the appropriate representations 5 (e.g., via grid files) may be faster, and 4 having SQL primitives for things like topology 3 is useful.

There are academic databases and 2 some commercial ones. Timecenter has some 1 links.

Another example of where a temporal database 16 is useful is where data changes over time. I 15 spent a few years working for an electricity 14 retailer where we stored meter readings 13 for 30 minute blocks of time. Those meter 12 readings could be revised at any point but 11 we still needed to be able to look back 10 at the history of changes for the readings.

We 9 therefore had the latest reading (our 'current 8 understanding' of the consumption for the 7 30 minutes) but could look back at our historic 6 understanding of the consumption. When you've 5 got data that can be adjusted in such a 4 way temporal databases work well.

(Having 3 said that, we hand carved it in SQL, but 2 it was a fair while ago. Wouldn't make that 1 decision these days.)