#IT #SQL #PostgreSQL #Datawarehouse #Datavault #Historisation #OLAP

Management of Temporal Data

The main purpose of this website is to present SQL code.
For theoretical background refer to:
“Managing Time in Relational Databases” by Tom Johnston & Randall Weis
or Wikipedia:

IT SQL PostgreSQL Datawarehouse Datavault Historisation

From Point in Time to Time Interval

To warm up – unitemporal:  Unitemporal Data
Main point – bitemporal: Generate Bitemporal Intervals

Merge Time Slices

Merging adjacent time slices:  Time Slice Compressor
Merging in bitemporal case:  Time Slice Compressor

Join Temporal Tables

Join unitemporal Tables:  Table Join
Join multi-temporal Tables: Multi-temporal Table Join

Overlap Free Time Slices

Overlap Free unitemporal: Overlap Free

Completeness of Time Slices

Gaplessness unitemporal: Gaplessness


Comments on overlaps:  Overlaps


English Language

The World of Data:  hdombrovskaya  github
Bitemporal History:  martinFowler
Wikipedia: Temporal Database

German Language

Wikipedia:  Temporale Datenhaltung
Fachartikel: Temporale referenzielle Integrität


English Language

C.J. Date, Hugh Darwen, and Nikos Lorentzos. 2014. Time and Relational Theory, Second Edition: Temporal Databases in the Relational Model and SQL (2nd ed.). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA.

Tom Johnston. 2014. Bitemporal Data: Theory and Practice (1st ed.). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA.

Tom Johnston and Randall Weis. 2010. Managing Time in Relational Databases: How to Design, Update and Query Temporal Data. Morgan Kaufmann Publishers Inc., San Francisco, CA, USA.

Krishna Kulkarni and Jan-Eike Michels. 2012. Temporal features in SQL:2011. SIGMOD Rec. 41, 3 (October 2012), 34-43.

Richard Thomas Snodgrass. 1999. Developing Time-Oriented Database Applications in SQL. Morgan Kaufmann Publishers Inc., San Francisco, CA, USA.

ISO/IEC 9075-2:2011, Information technology — Database languages — SQL , 2011

Martin Kaufmann, Peter M. Fischer, Norman May, Donald Kossmann: Benchmarking Bitemporal Database Systems: Ready for the Future or Stuck in the Past? EDBT 2014: 738-749

Margy Ross, Ralph Kimball: The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling
ISBN-13 ‏ : ‎ 978-1118530801

German Language

Modellierung von Business-Intelligence-Systemen von Michael Hahne, dpunkt.verlag

Thomas Myrach: Temporale Datenbanken in betrieblichen Informationssystemen: Prinzipien, Konzepte, Umsetzung (Teubner Reihe Wirtschaftsinformatik) 2005

Legal Disclosure

The translations into English with help of

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s