Developing time-oriented database applications in SQL
by Richard T. Snodgrass, Christian S. Jensen · first published 1999
67% recommend33% neutral
Why HN recommends it
Covers modeling data that changes over time in SQL, including valid time and transaction time. It is the standard reference on the subject, and its database patterns are useful even outside temporal applications.
Summarised by Claude from recommending comments
"Whether you're a database designer, programmer, analyst, or manager, you've probably encountered some of the challenges - and experienced some of the frustrations - associated with time-varying data. Where do you turn to fix the problem and see that it doesn't happen again? In Developing Time-Oriented Database Applications in SQL, a leading SQL researcher teaches you effective techniques for designing and building database applications that must integrate past and current data. Written to meet a pervasive, enduring need, this book will be indispensible if you happen to be part of the flurry
Affiliate links. As an Amazon Associate I earn from qualifying purchases.
Mentions per year
What Hacker News says
Related books
Managing Time in Relational Databases
Tom Johnston, Randall Weis
Recommended by 2 · mentioned by 42 recommend · 4 mention2 recommend · 42 recommend
2017–2024Applied Mathematics for Database Professionals
Lex deHaan, Toon Koppelaars
Recommended by 2 · mentioned by 32 recommend · 3 mention2 recommend · 32 recommend
2012–2024Applied mathematics for database professionals
Lex de Haan
Recommended by 5 · mentioned by 85 recommend · 8 mention5 recommend · 85 recommend
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.…
on “The Value of Bitemporality – Whose Time Is It Anyway?” ↗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…
on “Datomic: this is not the history you're looking for” ↗…suggest some ways to handle DDL changes over time (e.g. adding a new NOT NULL column, or changing a relationship from one-to-many to many-to-many). Here is your starting bibliography: 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, Bitemporal Data Magnus…
on “SQL is 43 years old – Here’s why we still use it today” ↗…called Andl that is another non-SQL relational query language. Personally, although I'm not a researcher, I've become intrigued by ways we model change-over-time in relational databases. Snodgrass's book _Developing Time-Oriented Database Applications in SQL_ is a good entry point to all that, and more recently we have SQL2011's Temporal standard. I still have a lot to read here myself, but I am keen to see what happens in the next ten years in how the relational…
on “The SQL filter clause: selective aggregates” ↗If I had six months to research something, I'd work on temporal databases, starting with the book Developing Time-Oriented Database Applications in SQL. I'd look at the history of attempts to bring this into the SQL standard, including the arguments between Snodgrass and Date, and what was eventually put in SQL2011. Then I'd see how much of this Postgres…
on “Ask HN: Any Ideas for Master's Thesis in Software Engineering?” ↗…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
on “InfluxDB is betting on Rust and Apache Arrow for next-gen data store” ↗- 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…
on “Ask HN: What are some good books for SQL/DBA?” ↗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…
on “Postgres audit tables saved us from taking down production” ↗I came here to plug "Developing Time-Oriented Database Applications in SQL", but you already did. Once you start developing systems w/ temporally-oriented schemas I find that it's difficult to go back. Queries can be painful (and arguably the functionality should be "baked-in"…
on “Taking the 'D' Out of CRUD with Soft Updates” ↗To piggyback on this, all developers dealing with this should read "Developing Time-Oriented Database Applications in SQL" too.
on “Storing UTC is not a silver bullet (2019)” ↗…they deal with changes to database schemas, types, etc. While googling I've found from this post: http://www.postgresql.org/message-id/Pine.GSO.4.64.080203152... this free (?) book - Snodgrass/Jensen "Developing Time-Oriented Database Applications in SQL" - http://www.cs.arizona.edu/people/rts/tdbbook.pdf Haven't read it, so I'll spend some time through it. To me this is an interresting thing, since I would like to know whether I can do history tracking…
on “Excision in Datomic” ↗Snodgrass wrote a whole book on that topic: Developing Time-Oriented Database Applications in SQL (1999). It is available as PDF on his publications page: https://www2.cs.arizona.edu/~rts/publications.html
on “Datomic Is Now Free” ↗Have you read Developing Time-Oriented Database Applications in SQL? I am very fond of ranges (actually it's exclusion constraints that are the key addition IMO), but they are not even close to what I'd like to have. I want `PRIMARY KEY` to work on temporal tables. And…
on “Ask HN: What are the cons of PostgreSQL?” ↗…--------------------- The pre-alpha version of "Project Writing for Nerds" [3] A cohort-based workshop to help fellow gentlenerds "spool brain to disk" with as little friction as possible. --------------------- [0] Developing Time-oriented Database Applications in SQl https://www2.cs.arizona.edu/~rts/tdbbook.pdf [1] Temporal Database Management https://people.cs.aau.dk/~csj/Thesis/ [2] Jepsen's helpful little notes on Consistency https://jepsen.io/consistency [3]…
on “Ask HN: What are you working on? (May 2025)” ↗