Hacker News Reader

Comment by pjungwir | Hacker News Reader

Hey I worked on this!

Thank you to everyone here saying they are excited about it. I often hear doubts that anyone wants this. Perhaps that's why vendors have been so slow to add it. And thank you 'bonesmoses for writing about it!

We are still missing system time, but if no one else wants to work on it, I hope to tackle that soon.

I have a lot of other ideas for improvement beyond SQL:2011, too. Here is a talk I gave last month about my personal roadmap:

https://illuminatedcomputing.com/pages/pgconf2026-temporal-r...

I've also been vibecoding a lisp REPL to play with the algebra of temporal relational operators (important for the planner): https://github.com/pjungwir/relsim

That overlaps with my attempt to write implementations for temporal semi/anti/outer-join and other relops: https://github.com/pjungwir/temporal_ops

If anyone has comments about what you'd like to see, I'm happy for feedback!

View on HN

> I often hear doubts that anyone wants this.

This was actually a key feature that was greatly desired by a large organization's HR function when they were working to setup a consolidated enterprise data warehouse a decade back.

It would have made it much easier for retention specialists to be able to do things like answer how much a missed retention bonus should have been once the paperwork had been fixed up.

Likewise for the same organization's board for correction of records, and in general any offices that have to make sense of what the world was like for the computer-based records years ago, and how that would change if certain data would have been different years ago.

There’s definitely interest in this in finance domains. I’ve done DATERANGE and GiST exclude constraints based solutions for a symbology database for example, where any given ticker might represent different securities at different times without overlap, and relying on functions to keep the dateranges in sync when updating a row.

So basically what WHITOUT OVERLAPS and FOR PORTION OF do.

The system time is also interesting in the context of financial data and backtesting, as companies might republish a statement with corrections, and it would help tracking why the system made a decision at a given time.

This bridges the gap to something like xtdb.

Ok this looks like interesting, and now rebuilding my ERP it sounds the kind stuff I need to look at.

Question: Could this be useful to store history of changes? One pesky trouble is that after you close an invoice all the data there becomes immutable but you need to continue change the base tables.

So, I have a convoluted way of double write rows and keep the "current" as main. All for "just" the case that an invoice need to see the data as was when finalized.

Is this feature for this case too?

I think yes. You're saying that the invoice is generated from other data in separate tables, and it should be immutable even if that data changes. So today you are copying everything used by the invoice.

That's exactly why I started learning about temporal tables. I had a customer whose app used questionnaires to measure the effectiveness of government social services, and they let people change the questions (and multiple choice options) even after there were answers! Obviously the data was trashed.

I've seen this bug over & over again, where you have a foreign key relationship, and the referenced table changes, but the referencing table needs the old data. Another example is a sale that doesn't capture the product's current price.

Temporal tables mean that you can run your 2022 financial reports and get the same answer you got before.

My own time-tracking and invoicing app has to solve this problem. I also "copy everything" when something changes. I just gave a talk about migrating it to temporal tables: https://illuminatedcomputing.com/pages/pgdata2026-migrating-...

You'll have to decide if your use-case is more "system time" (history of the database) or "application time" (history of the entities). The features here are for application time. I want to make sure Postgres gets system time too, but it's not in v19.

If you want system time today, there are several widely-adopted Postgres extensions that can do that already. I cover them here: https://illuminatedcomputing.com/posts/2017/12/temporal-data...

And actually, application time is managed by you (while system time is managed automatically by the database), so really you can use it for whatever you want.

How tied is the implementation to time specifically?

Can it apply to other types (eg geometries) which can be subdivided?

In the Postgres version, you can use a rangetype or multirangetype over any base type (integers, inets, frammishes, etc). This is a generalization of SQL:2011, where PERIODs have to be on date/timestamp/timestamptz columns. I have ambitions to support non-range/multirange types as well, even user defined types, but I'll need to add a way for them to communicate a few things to core Postgres, like how to do intersect/contains/overlaps. I talked about that in my "roadmap" talk linked here if you're interested in details.

Thanks!

Awesome! I'll look forward to that. :)

Reply on news.ycombinator.com