Temporal database

A temporal database stores data relating to time instances. It offers temporal data types and stores information relating to past, present and future time. Temporal databases could be uni-temporal, bi-temporal or tri-temporal.

More specifically the temporal aspects usually include valid time, transaction time or decision time

  • Valid time is the time period during which a fact is true in the real world.
  • Transaction time is the time period during which a fact stored in the database was known.
  • Decision time is the time period during which a fact stored in the database was decided to be valid.


A uni-temporal database has one axis of time, either the validity range or the system time range.


A bi-temporal database has two axes of time.

  • valid time.
  • transaction time or decision time.


A tri-temporal database has three axes of time.

  • valid time.
  • transaction time
  • decision time.

This approach introduces additional complexities.

Temporal databases are in contrast to current databases (not to be confused with currently available databases), which store only facts which are believed to be true at the current time.


Temporal databases support managing and accessing temporal data by providing one or more of the following features:[1][2]

  • A time period datatype, including the ability to represent time periods with no end (infinity or forever)
  • The ability to define valid and transaction time period attributes and bitemporal relations
  • System-maintained transaction time
  • Temporal primary keys, including non-overlapping period constraints
  • Temporal constraints, including non-overlapping uniqueness and referential integrity
  • Update and deletion of temporal records with automatic splitting and coalescing of time periods
  • Temporal queries at current time, time points in the past or future, or over durations
  • Predicates for querying time periods, often based on Allen’s interval relations


With the development of SQL and its attendant use in real-life applications, database users realized that when they added date columns to key fields, some issues arose. For example, if a table has a primary key and some attributes, adding a date to the primary key to track historical changes can lead to creation of more rows than intended. Deletes must also be handled differently when rows are tracked in this way. In 1992, this issue was recognized but standard database theory was not yet up to resolving this issue, and neither was the then-newly formalized SQL-92 standard.

Richard Snodgrass proposed in 1992 that temporal extensions to SQL be developed by the temporal database community. In response to this proposal, a committee was formed to design extensions to the 1992 edition of the SQL standard (ANSI X3.135.-1992 and ISO/IEC 9075:1992); those extensions, known as TSQL2, were developed during 1993 by this committee.[3] In late 1993, Snodgrass presented this work to the group responsible for the American National Standard for Database Language SQL, ANSI Technical Committee X3H2 (now known as NCITS H2). The preliminary language specification appeared in the March 1994 ACM SIGMOD Record. Based on responses to that specification, changes were made to the language, and the definitive version of the TSQL2 Language Specification was published in September, 1994[4]

An attempt was made to incorporate parts of TSQL2 into the new SQL standard SQL:1999, called SQL3. Parts of TSQL2 were included in a new substandard of SQL3, ISO/IEC 9075-7, called SQL/Temporal.[3] The TSQL2 approach was heavily criticized by Chris Date and Hugh Darwen.[5] The ISO project responsible for temporal support was canceled near the end of 2001.

As of December 2011, ISO/IEC 9075, Database Language SQL:2011 Part 2: SQL/Foundation included clauses in table definitions to define "application-time period tables" (valid time tables), "system-versioned tables" (transaction time tables) and "system-versioned application-time period tables" (bitemporal tables). A substantive difference between the TSQL2 proposal and what was adopted in SQL:2011 is that there are no hidden columns in the SQL:2011 treatment, nor does it have a new data type for intervals; instead two date or timestamp columns can be bound together using a PERIOD FOR declaration. Another difference is replacement of the controversial (prefix) statement modifiers from TSQL2 with a set of temporal predicates.[1]

Other features of SQL:2011 standard related with temporal databases are the Automatic time period splitting, Temporal primary keys, Temporal referential integrity, Temporal predicates with Allen's interval algebra and Time-sliced and sequenced queries.


For illustration, consider the following short biography of a fictional man, John Doe:

John Doe was born on April 3, 1975 in the Kids Hospital of Medicine County, as son of Jack Doe and Jane Doe who lived in Smallville. Jack Doe proudly registered the birth of his first-born on April 4, 1975 at the Smallville City Hall. John grew up as a joyful boy, turned out to be a brilliant student and graduated with honors in 1993. After graduation, he went to live on his own in Bigtown. Although he moved out on August 26, 1994, he forgot to register the change of address officially. It was only at the turn of the seasons that his mother reminded him that he had to register, which he did a few days later on December 27, 1994. Although John had a promising future, his story ends tragically. John Doe was accidentally hit by a truck on April 1, 2001. The coroner reported his date of death on the very same day.

Using a no temporal database

To store the life of John Doe in a current (non-temporal) database we use a table Person (Name, Address). (In order to simplify Name is defined as the primary key of Person.)

John's father officially reported his birth on April 4, 1975. On this date a Smallville official inserted the following entry in the database: Person(John Doe, Smallville). Note that the date itself is not stored in the database.

After graduation, John moves out, but forgets to register his new address. John's entry in the database is not changed until December 27, 1994, when he finally reports it. A Bigtown official updates his address in the database. The Person table now contains Person(John Doe, Bigtown). Note that the information of John living in Smallville has been overwritten, so it is no longer possible to retrieve that information from the database. An official accessing the database on December 28, 1994 would be told that John lives in Bigtown. More technically: if a database administrator ran the query SELECT ADDRESS FROM PERSON WHERE NAME='John Doe' on December 26, 1994, the result would be Smallville. Running the same query 2 days later would result in Bigtown.

Until his death, the database would state that he lived in Bigtown. On April 1, 2001, the coroner deletes the John Doe entry from the database. After this, running the above query would return no result at all.

Date Real world event Database Action What the database shows
April 3, 1975 John is born Nothing There is no person called John Doe
April 4, 1975 John's father officially reports John's birth Inserted:Person(John Doe, Smallville) John Doe lives in Smallville
August 26, 1994 After graduation, John moves to Bigtown, but forgets to register his new address Nothing John Doe lives in Smallville
December 26, 1994 Nothing Nothing John Doe lives in Smallville
December 27, 1994 John registers his new address Updated:Person(John Doe, Bigtown) John Doe lives in Bigtown
April 1, 2001 John dies Deleted:Person(John Doe) There is no person called John Doe

Using Single Axis : Valid time or Transaction Time

Valid time is the time for which a fact is true in the real world. A valid time period may be in the past, span the current time, or occur in the future.

For the example above, to record valid time, the Person table has two fields added, Valid-From and Valid-To. These specify the period when a person's address is valid in the real world. On April 4, 1975 John's father registered his son's birth. An official then inserts a new entry into the database stating that John lives in Smallville from April 3. Note that although the data was inserted on the 4th, the database states that the information is valid since the 3rd. The official does not yet know if or when John will move to another place, so the Valid-To field is set to infinity (∞). The entry in the database is:

Person(John Doe, Smallville, 3-Apr-1975, ∞).

On December 27, 1994 John reports his new address in Bigtown where he has been living since August 26, 1994. A new database entry is made to record this fact:

Person(John Doe, Bigtown, 26-Aug-1994, ∞).

The original entry Person (John Doe, Smallville, 3-Apr-1975, ∞) is not deleted, but has the Valid-To attribute updated to reflect that it is now known that John stopped living in Smallville on August 26, 1994. The database now contains two entries for John Doe

Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994).
Person(John Doe, Bigtown, 26-Aug-1994, ∞).

When John dies his current entry in the database is updated stating that John does not live in Bigtown any longer. The database now looks like this

Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994).
Person(John Doe, Bigtown, 26-Aug-1994, 1-Apr-2001).

Using Two Axes : Valid Time and Transaction time

Transaction time records the time period during which a database entry is accepted as correct. This enables queries that show the state of the database at a given time. Transaction time periods can only occur in the past or up to the current time. In a transaction time table, records are never deleted. Only new records can be inserted, and existing ones updated by setting their transaction end time to show that they are no longer current.

To enable transaction time in the example above, two more fields are added to the Person table: Transaction-From and Transaction-To. Transaction-From is the time a transaction was made, and Transaction-To is the time that the transaction was superseded (which may be infinity if it has not yet been superseded). This makes the table into a bitemporal table.

What happens if the person's address as stored in the database is incorrect? Suppose an official accidentally entered the wrong address or date? Or, suppose the person lied about their address for some reason. Upon discovery of the error, the officials update the database to correct the information recorded.

For example, from 1-Jun-1995 to 3-Sep-2000, John Doe moved to Beachy. But to avoid paying Beachy's exorbitant residence tax, he never reported it to the authorities. Later during a tax investigation, it is discovered on 2-Feb-2001 that he was in fact in Beachy during those dates. To record this fact, the existing entry about John living in Bigtown must be split into two separate records, and a new record inserted recording his residence in Beachy. The database would then appear as follows:

Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994).
Person(John Doe, Bigtown, 26-Aug-1994, 1-Jun-1995).
Person(John Doe, Beachy, 1-Jun-1995, 3-Sep-2000).
Person(John Doe, Bigtown, 3-Sep-2000, 1-Apr-2001).

However, this leaves no record that the database ever claimed that he lived in Bigtown during 1-Jun-1995 to 3-Sep-2000. This might be important to know for auditing reasons, or to use as evidence in the official's tax investigation. Transaction time allows capturing this changing knowledge in the database, since entries are never directly modified or deleted. Instead, each entry records when it was entered and when it was superseded (or logically deleted). The database contents then look like this:

Name, City, Valid From, Valid Till, Entered, Superseded
Person(John Doe, Smallville, 3-Apr-1975,  ∞,           4-Apr-1975,  27-Dec-1994).
Person(John Doe, Smallville, 3-Apr-1975,  26-Aug-1994, 27-Dec-1994, ∞          ).
Person(John Doe, Bigtown,    26-Aug-1994, ∞,           27-Dec-1994, 2-Feb-2001 ).
Person(John Doe, Bigtown,    26-Aug-1994, 1-Jun-1995,  2-Feb-2001,  ∞          ).
Person(John Doe, Beachy,     1-Jun-1995,  3-Sep-2000,  2-Feb-2001,  ∞          ).
Person(John Doe, Bigtown,    3-Sep-2000,  ∞,           2-Feb-2001,  1-Apr-2001 ).
Person(John Doe, Bigtown,    3-Sep-2000,  1-Apr-2001,  1-Apr-2001,  ∞          ).

The database records not only what happened in the real world, but also what was officially recorded at different times.

Bitemporal Modelling

A bitemporal model contains both valid and transaction time. This provides both historical and rollback information. Historical information (e.g.: "Where did John live in 1992?") is provided by the valid time. Rollback (e.g.: "In 1992, where did the database believe John lived?") is provided by the transaction time. The answers to these example questions may not be the same – the database may have been altered since 1992, causing the queries to produce different results.

The valid time and transaction time do not have to be the same for a single fact. For example, consider a temporal database storing data about the 18th century. The valid time of these facts is somewhere between 1701 and 1800. The transaction time would show when the facts were inserted into the database (for example, January 21, 1998).

Schema evolution

A challenging issue is the support of temporal queries in a transaction time database under evolving schema. In order to achieve perfect archival quality it is of key importance to store the data under the schema version under which they first appeared. However, even the most simple temporal query rewriting the history of an attribute value would be required to be manually rewritten under each of the schema versions, potentially hundreds as in the case of MediaWiki [1]. This process would be particularly taxing for users. A proposed solution is to provide automatic query rewriting,[6][7] although this is not part of SQL or similar standards.

Approachs to minimize the complexities of schema evolution is

  • to use a semi-structured database/NoSQL database which reduces the complexities of modeling atribute data but provides no features for handling the 2 time axes.[8]
  • to use a database capable of storing both semi-structured data for attributes and structured data for time axes (eg SnowflakeDB, PostgreSQL)

Implementations in notable products

The following implementations provide temporal features in a relational database management system (RDBMS).

  • MariaDB version 10.3.4 added support for SQL:2011 standard as "System-Versioned Tables".[9]
  • Oracle Database – Oracle Workspace Manager is a feature of Oracle Database which enables application developers and DBAs to manage current, proposed and historical versions of data in the same database.
  • PostgreSQL version 9.2 added native ranged data types that are capable of implementing all of the features of the pgFoundry temporal contributed extension.[10][11] The PostgreSQL range types are supported by numerous native operators and functions.
  • Teradata provides two products. Teradata version 13.10 and Teradata version 14 have temporal features based on TSQL2[12] built into the database.
  • IBM DB2 version 10 added a feature called "time travel query"[2] which is based on the temporal capabilities of the SQL:2011 standard.[1]
  • Microsoft SQL Server introduced Temporal Tables as a feature for SQL Server 2016. The feature is described in a video on Microsoft's "Channel 9" web site.[13]

Non-relational, NoSQL database management systems that provide temporal features including the following:

  • MarkLogic introduced bitemporal data support in version 8.0. Time stamps for Valid and System time are stored in JSON or XML documents.[14]
  • SirixDB stores snapshots of (currently) XML- and JSON-documents very efficiently in a binary format due to a novel versioning algorithm called sliding snapshot, which balances read-/write-performance and never creates write peaks. Time-travel queries are supported natively as well as diffing functions.


Scd model
Scd model example
(click on image to see)

Sometimes the Slowly changing dimension is used as a method.

Further reading

  • C.J. Date, Hugh Darwen, Nikos Lorentzos (2002). Temporal Data & the Relational Model, First Edition (The Morgan Kaufmann Series in Data Management Systems); Morgan Kaufmann; 1st edition; 422 pages. ISBN 1-55860-855-9.
  • Joe Celko (2014). Joe Celko's SQL for Smarties: Advanced SQL Programming (The Morgan Kaufmann Series in Data Management); Morgan Kaufmann; 5th edition. ISBN 978-0-12-800761-7.—Chapters 12 and 35 in particular discuss temporal issues.
  • Snodgrass, Richard T. (1999). "Developing Time-Oriented Database Applications in SQL " (PDF). (4.77 MiB) (Morgan Kaufmann Series in Data Management Systems); Morgan Kaufmann; 504 pages; ISBN 1-55860-436-7

See also


  1. ^ a b c Kulkarni, Krishna, and Jan-Eike Michels. "Temporal features in SQL: 2011". ACM SIGMOD Record 41.3 (2012): 34-43.
  2. ^ a b http://www.ibm.com/developerworks/data/library/techarticle/dm-1204db2temporaldata/
  3. ^ a b Snodgrass, 1999, p. 9
  4. ^ Richard T. Snodgrass. "TSQL2 Temporal Query Language". www.cs.arizona.edu. Computer Science Department of the University of Arizona. Retrieved 14 July 2009.
  5. ^ Hugh Darwen, C.J. Date, “An overview and Analysis of Proposals Based on the TSQL2 Approach”, In Date on Database: Writings 2000-2006, C.J. Date, Apress, 2006, pp. 481-514
  6. ^ Hyun J. Moon; Carlo A. Curino; Alin Deutsch; C.-Y. Hou & Carlo Zaniolo (2008). Managing and querying transaction-time databases under schema evolution. Very Large Data Base VLDB.
  7. ^ Hyun J. Moon; Carlo A. Curino & Carlo Zaniolo (2010). Scalable Architecture and Query Optimization for Transaction-time DBs with Evolving Schemas. SIGMOD.
  8. ^ Anthony B. Coates (2015). Why Banks Care About Bitemporality. MarkLogic World 2015.
  9. ^ https://mariadb.com/kb/en/library/system-versioned-tables/
  10. ^ Paquier, Michael (1 November 2012). "Postgres 9.2 highlight: range types". Michael Paquier - Open source developer based in Japan. Archived from the original on 2016-04-23.
  11. ^ Katz, Jonathan S. "Range Types: Your Life Will Never Be The Same" (PDF). Retrieved 14 July 2014.
  12. ^ Al-Kateb, Mohammed et al. "Temporal Query Processing in Teradata". EDBT/ICDT ’13 March 18–22, 2013, Genoa, Italy
  13. ^ http://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016
  14. ^ Bridgwater, Adrian (24 November 2014). "Data Is Good, 'Bidirectionalized Bitemporal' Data Is Better".

External links


An astrarium, also called a planetarium, is the mechanical representation of the cyclic nature of astronomical objects in one timepiece. It is an astronomical clock.

BPL (time service)

BPL is the call sign of the official long-wave time signal service of the People's Republic of China, operated by the Chinese Academy of Sciences, broadcasting on 100 kHz from CAS's National Time Service Center in Pucheng County, Shaanxi at 34°56′54″N 109°32′34″E, roughly 70 km northeast of Lintong, along with NTSC's short-wave time signal BPM on 2.5, 5.0, 10.0, and 15.0 MHz.

BPL broadcasts LORAN-C compatible format signal from 5:30 to 13:30 UTC, using an 800 kW transmitter covering a radius up to 3000 km.

Bitemporal Modeling

Bitemporal Modeling is a specific case of Temporal database information modeling technique designed to handle historical data along two different timelines. This makes it possible to rewind the information to "as it actually was" in combination with "as it was recorded" at some point in time. In order to be able to do so, information cannot be discarded even if it is erroneous. Within, for example, financial reporting it is often desirable to be able to recreate an old report both as it actually looked at the time of creation and as it should have looked given corrections made to the data after its creation.

Implementations of Bitemporal Modeling are mostly done using relational databases. As such, Bitemporal Modeling is considered different from Dimensional Modeling and complementary to database normalization. The SQL:2011 standard provides language constructs for working with bitemporal data. However, many of current solutions are still vendor-specific.

Carpe diem

Carpe diem is a Latin aphorism, usually translated "seize the day", taken from book 1 of the Roman poet Horace's work Odes (23 BC).


Chronometry (from Greek χρόνος chronos, "time" and μέτρον metron, "measure") is the science of the measurement of time, or timekeeping. Chronometry applies to electronic devices, while horology refers to mechanical devices.

It should not to be confused with chronology, the science of locating events in time, which often relies upon it.

Clock position

A clock position is the relative direction of an object described using the analogy of a 12-hour clock to describe angles and directions. One imagines a clock face lying either upright or flat in front of oneself, and identifies the twelve hour markings with the directions in which they point.

Using this analogy, 12 o'clock means ahead or above, 3 o'clock means to the right, 6 o'clock means behind or below, and 9 o'clock means to the left. The other eight hours refer to directions that are not directly in line with the four cardinal directions.

In aviation, a clock position refers to a horizontal direction; it may be supplemented with the word high or low to describe the vertical direction which is pointed towards your feet. 6 o'clock high means behind and above the horizon, while 12 o'clock low means ahead and below the horizon.

Common year

A common year is a calendar year with 365 days, as distinguished from a leap year, which has 366. More generally, a common year is one without intercalation. The Gregorian calendar, (like the earlier Julian calendar), employs both common years and leap years to keep the calendar aligned with the tropical year, which does not contain an exact number of days.

The common year of 365 days has 52 weeks and one day, hence a common year always begins and ends on the same day of the week (for example, January 1 and December 31 fell on a Sunday in 2017) and the year following a common year will start on the subsequent day of the week. In common years, February has four weeks, so March will begin on the same day of the week. November will also begin on this day.

In the Gregorian calendar, 303 of every 400 years are common years. By comparison, in the Julian calendar, 300 out of every 400 years are common years, and in the Revised Julian calendar (used by Greece) 682 out of every 900 years are common years.


Endurantism or endurance theory is a philosophical theory of persistence and identity. According to the endurantist view, material objects are persisting three-dimensional individuals wholly present at every moment of their existence, which goes with an A-theory of time. This conception of an individual as always present is opposed to perdurantism or four dimensionalism, which maintains that an object is a series of temporal parts or stages, requiring a B-theory of time. The use of "endure" and "perdure" to distinguish two ways in which an object can be thought to persist can be traced to David Lewis.


HD2IOA is the callsign of a time signal radio station operated by the Navy of Ecuador. The station is located at Guayaquil, Ecuador and transmits in the HF band on 3.81 and 7.6 MHz.The transmission is in AM mode with only the lower sideband (part of the time H3E and the rest H2B/H2D) and consists of 780 Hz tone pulses repeated every ten seconds and voice announcements in Spanish.

While sometimes this station is described as defunct, reception reports of this station on 3.81 MHz appear regularly at the Utility DX Forum.

Hexadecimal time

Hexadecimal time is the representation of the time of day as a hexadecimal number in the interval [0,1).

The day is divided into 1016 (1610) hexadecimal hours, each hour into 10016 (25610) hexadecimal minutes, and each minute into 1016 (1610) hexadecimal seconds.

Intercalation (timekeeping)

Intercalation or embolism in timekeeping is the insertion of a leap day, week, or month into some calendar years to make the calendar follow the seasons or moon phases. Lunisolar calendars may require intercalations of both days and months.


The minute is a unit of time or angle. As a unit of time, the minute is most of times equal to ​1⁄60 (the first sexagesimal fraction) of an hour, or 60 seconds. In the UTC time standard, a minute on rare occasions has 61 seconds, a consequence of leap seconds (there is a provision to insert a negative leap second, which would result in a 59-second minute, but this has never happened in more than 40 years under this system). As a unit of angle, the minute of arc is equal to ​1⁄60 of a degree, or 60 seconds (of arc). Although not an SI unit for either time or angle, the minute is accepted for use with SI units for both. The SI symbols for minute or minutes are min for time measurement, and the prime symbol after a number, e.g. 5′, for angle measurement. The prime is also sometimes used informally to denote minutes of time.

Spatiotemporal database

A spatiotemporal database is a database that manages both space and time information. Common examples include:

Tracking of moving objects, which typically can occupy only a single position at a given time.

A database of wireless communication networks, which may exist only for a short timespan within a geographic region.

An index of species in a given geographic region, where over time additional species may be introduced or existing species migrate or die out.

Historical tracking of plate tectonic activity.Spatiotemporal databases are an extension of spatial databases. A spatiotemporal database embodies spatial, temporal, and spatiotemporal database concepts, and captures spatial and temporal aspects of data and deals with:

geometry changing over time and/or

location of objects moving over invariant geometry (known variously as moving objects databases or real-time locating systems).

Specious present

The specious present is the time duration wherein one's perceptions are considered to be in the present. Time perception studies the sense of time, which differs from other senses since time cannot be directly perceived but must be reconstructed by the brain.

Surrogate key

A surrogate key (or synthetic key, entity identifier, system-generated key, database sequence number, factless key, technical key, or arbitrary unique identifier) in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural (or business) key which is derived from application data.

Tomorrow (time)

Tomorrow is a temporal construct of the relative future; literally of the day after the current day (today), or figuratively of future periods or times. Tomorrow is usually considered just beyond the present and counter to yesterday. It is important in time perception because it is the first direction the arrow of time takes humans on Earth.

UTC offset

The UTC offset is the difference in hours and minutes from Coordinated Universal Time (UTC) for a particular place and date. It is generally shown in the format ±[hh]:[mm], ±[hh][mm], or ±[hh]. So if the time being described is one hour ahead of UTC (such as the time in Berlin during the winter), the UTC offset would be "+01:00", "+0100", or simply "+01".

Every inhabited place in the world has a UTC offset that is a multiple of 15 minutes, and the majority of offsets (as well as all nautical time zones) are measured in whole hours.

UTC is the equivalent to GMT.


YVTO is the callsign of the official time signal from the Juan Manuel Cagigal Naval Observatory in Caracas, Venezuela. The content of YVTO's signal, which is a continuous 1 kW amplitude modulated carrier wave at 5.000 MHz, is much simpler than that broadcast by some of the other time signal stations around the world, such as WWV.

The methods of time transmission from YVTO are very limited. The broadcast employs no form of digital time code. The time of day is given in Venezuelan Standard Time (VET), and is only sent using Spanish language voice announcements. YVTO also transmits 100 ms-long beeps of 1000 Hz every second, except for thirty seconds past the minute. The top of the minute is marked by a 0.5 second 800 Hz tone.The station previously broadcast on 6,100 MHz but appears to have changed to the current frequency by 1990.

Yesterday (time)

Yesterday is a temporal construct of the relative past; literally of the day before the current day (today), or figuratively of earlier periods or times, often but not always within living memory.

This page is based on a Wikipedia article written by authors (here).
Text is available under the CC BY-SA 3.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.