https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs...
Postgres itself has not yet added such to the core, since it moves about as fast as an elephant. There are extensions that do implement it (https://wiki.postgresql.org/wiki/Temporal_Extensions).
WHERE CURRENT_TIMESTAMP BETWEEN start AND end is much better than WHERE deleted_at IS NOT NULL
Also, if we are talking about real use case of audit, not a simplified artificial one, my real tables were more like
changeset(id INT, timestamp DATETIME, user ^USER, host TEXT, ip_address TEXT, <other security data>)
datatable1(id INT, created_at ^changeset, updated_at ^changeset, deleted_at ^changeset NULL, <other fields>)
datatable2(id INT, created_at ^changeset, updated_at ^changeset, deleted_at ^changeset NULL, <other fields>)
datatable3(id INT, created_at ^changeset, updated_at ^changeset, deleted_at ^changeset NULL, <other fields>)
https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs...
Postgres itself has not yet added such to the core, since it moves about as fast as an elephant. There are extensions that do implement it (https://wiki.postgresql.org/wiki/Temporal_Extensions).