Desired feature:

SELECT * FROM t AS OF SYSTEM TIME '-1h', u AS OF SYSTEM TIME '-2h'

Or, more usefully:

INSERT INTO t1 SELECT * FROM t2 AS OF SYSTEM TIME '-1h'

CREATE TABLE t AS SELECT * FROM t AS OF SYSTEM TIME '-1h'

Update: #142147 has addressed this specifically for CREATE TABLE AS statements.

The idea here is that currently, the same client.Txn object is used for an entire statement, but there is no reason why this should remain true.

Single read-only relational nodes, especially table readers, could use a different Txn object with a historical timestamp.

Epic: CRDB-9838

Jira issue: CRDB-4561