Developing time-oriented database applications in SQL

What Hacker News says

1. _boffin_May 2022
   - Bitemporal Databases: Modeling and Implementation: https://www.amazon.com/Bitemporal-Databases-Canan-Eren-Atay/...

   - Developing Time-Oriented Database Applications in SQL by Rich. Snodgrass: https://www.amazon.com/Developing-Time-Oriented-Database-App...

   -----

   Anything by Joe Celko:

   - SQL for Smarties: https://www.amazon.com/gp/product/B00R17NZZC

   - SQL Programming Style: https://www.amazon.com/gp/product/B006L21AO6

   - Trees and Hierarchies in SQL for Smarties: https://www.amazon.com/gp/product/B006Y8MKUU

   - Thinking in Sets: https://www.amazon.com/gp/product/B001AODZZC

   -------

   - SQL Antipatterns by Bill Karwin: https://www.amazon.com/SQL-Antipatterns-Programming-Pragmati...

   Show morethread
2. BatteryMountainMar 2022
   To piggyback on this, all developers dealing with this should read "Developing Time-Oriented Database Applications in SQL" too.
   thread
3. BatteryMountainOct 2021
   If anyone feel interested after reading this comment, checkout the book called "Developing Time-Oriented Database Applications in SQL".

   edit: it contains many database-patterns that are useful even you aren't building time-oriented applications. It will make you a better developer.

   edit 2: basically, once you know some things from this book, every time you encounter a new database engine or programming language, the first thing you will want to look at is how the language handles dates & times - suddenly this kind of field become way more interesting. So the book might change how you view dates/times permanently, just be careful if you tend to over-engineer or are perfectionist, because it makes it easy to do so. Apply the patterns when it makes pragmatic sense.

   Show morethread
4. jacques_chesterNov 2020
   I think you're on the edge of describing bitemporal databases. You have a range which represents "time over which this fact is asserted to be true" and a second range for "time during which the assertion was recorded as valid". These typically get called "valid time" and "transaction time".

   You can express forecasts this way. The valid time range is the range for which you assert a given forecast is going to be true. The transaction time range is the time during which you held that forecast to be the current forecast.

   Using transaction time you can then reconstruct your state of belief from any point in time. Using valid time you can make assertions about any fact in ranges over the past, present or future.

   I think Snodgrass's Developing Time-Oriented Database Applications in SQL[0] is still the best overall introduction, though the SQL used is fairly dated (ha). The relevant Wikipedia entry is OK too[1].

   [0] https://www2.cs.arizona.edu/~rts/tdbbook.pdf

   [1] https://en.wikipedia.org/wiki/Temporal_database

   Show morethread
5. michelppApr 2019
   There are a couple of good books on bitemporality, Richard Snodgrass' "Developing time-oriented database applications in SQL" is pretty much the bible on the subject and goes into great detail. "Managing Time in Relational Databases" by Tom Johnston and Randall Weis is another great book, a little more hands on with the code.

   I'd love to see someone translate the concepts in both books to a useful postgres package. As Snodgrass points out, "Unfortunately, due to the noncompliance of all existing DBMSs, a few of these fragments run on no existing platform" and thus only discusses how they can be applied to DB2, MSSQL, Sybase, Oracle8 and UniSQL.

   Show morethread
6. pjungwirJul 2017
   Temporal tables are a pet interest of mine, so here are some links that people might find useful:

   Richard Snodgrass, Developing Time-Oriented Database Applications in SQL.

   Hugh Darwen & C.J. Date, "An Overview and Analysis of Proposals Based on the TSQL2 Approach".

   Krishna Kulkarni & Jan-Eike Michels, "Temporal features in SQL:2011".

   Tom Johnston, Managing Time in Relational Databases. (I haven't read this one yet.)

   Tom Johnston, Bitemporal Data.

   Magnus Hagander, "A TARDIS for Your ORM": https://www.youtube.com/watch?v=TRgni5q0YM8

   Also I've yet to see anyone even talk about managing DDL changes in temporal databases (well Magnus mentions that he won't cover it), but it's the same problem as discussed here. It's very practical, but you could write a dissertation or a book about it.

   Show morethread