There is still one significant difference of my prototype implementation with SQL standard: it associates timestamp with select statement, not with particular table. It seems to be more difficult to support and I am not sure that joining tables from different timelines has much sense. But certainly it also can be fixed.
I think the main use I would find for this feature is something like:
select * from foo except select * from foo as old_foo as of '<some time>';
Just a quick report from the world of ORMs and web applications.
Today the idiomatic approach for an ORM like Ruby on Rails is to support temporal(ish) queries using three additional TIMESTAMP_TZ columns: "created_at", "updated_at" and "deleted_at". This idiom is bundled up into a plugin called "acts_as_paranoid" (See: https://github.com/rubysherpas/paranoia). We used this extensively at Heroku in our production code for auditability reasons.
In general, this gets implemented on a per-table basis and usually has no expiry short of manual cleanup. (It would be interesting to contemplate how an end-user would clean up a table without losing their entire history in the event of some kind of bug or bloat.)
I think a quality PostgreSQL-core implementation would be a fantastic enhancement, though it would obviously introduce a bunch of interesting decisions around how to handle things like referential integrity.
Personally, I frequently used these columns to query for things like "how many users were created in each of the last twelve months", and the ability to index on those dates was often important.
I'm confident that if this feature made it into PostgreSQL there would be interested people in downstream communities that would take advantage of it.
Hope all that helps,
--
Peter van Hardenberg San Francisco, California "Everything was beautiful, and nothing hurt."—Kurt Vonnegut