Can my DB?

• No built-in temporal tables. The temporal_tables extension provides a trigger-based implementation of system-period tables.
• pgaudit is a separate concern — audit logging, not versioned queries.

• Not supported. Trigger-based audit tables are the usual substitute.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2),
  row_start DATETIME(6) GENERATED ALWAYS AS ROW START,
  row_end   DATETIME(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(row_start, row_end)
) WITH SYSTEM VERSIONING;

-- Query current data
SELECT * FROM employees;

-- Query as of a specific point in time
SELECT * FROM employees
  FOR SYSTEM_TIME AS OF '2023-01-01 00:00:00';

-- Query all versions including history
SELECT * FROM employees
  FOR SYSTEM_TIME ALL;

• Added in 10.3.4, one of the first open-source implementations of SQL:2011.
• History lives in the same table, optionally partitioned out via PARTITION BY SYSTEM_TIME.
• FOR SYSTEM_TIME accepts AS OF, BETWEEN ... AND ..., FROM ... TO ..., and ALL.
• Existing tables can be converted with ALTER TABLE ... ADD SYSTEM VERSIONING.

-- Enable Flashback Data Archive on a table
ALTER TABLE employees FLASHBACK ARCHIVE;

-- Query as of a specific timestamp
SELECT * FROM employees
  AS OF TIMESTAMP TO_TIMESTAMP('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- Query as of a specific System Change Number (SCN)
SELECT * FROM employees
  AS OF SCN 1234567;

-- Query across a time range
SELECT * FROM employees
  VERSIONS BETWEEN TIMESTAMP
    TO_TIMESTAMP('2023-01-01', 'YYYY-MM-DD')
    AND TO_TIMESTAMP('2023-06-01', 'YYYY-MM-DD');

• Syntax predates SQL:2011. AS OF TIMESTAMP / AS OF SCN reads a single point in time; VERSIONS BETWEEN returns every row version across a range.
• Flashback Data Archive must be enabled (ALTER TABLE ... FLASHBACK ARCHIVE) and history lives in a configurable separate tablespace with a retention period.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name NVARCHAR(100),
  salary DECIMAL(10,2),
  valid_from DATETIME2 GENERATED ALWAYS AS ROW START,
  valid_to   DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to)
) WITH (SYSTEM_VERSIONING = ON
        (HISTORY_TABLE = dbo.employees_history));

-- Query as of a specific point in time
SELECT * FROM employees
  FOR SYSTEM_TIME AS OF '2023-01-01T00:00:00';

-- Query across a time range
SELECT * FROM employees
  FOR SYSTEM_TIME BETWEEN '2023-01-01' AND '2023-06-01';

• Added in 2016; also on Azure SQL Database.
• History is kept in a separate (explicit or implicit) HISTORY_TABLE. Queries with FOR SYSTEM_TIME are routed to it automatically.

• Not supported. Track history in application code or with triggers.

• Not supported. Maintain a parallel history table via ETL or stored procedures.

CREATE TABLE employees (
  id INT NOT NULL,
  name VARCHAR(100),
  salary DECIMAL(10,2),
  row_begin  TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
  row_end    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
  trans_start TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID,
  PERIOD SYSTEM_TIME(row_begin, row_end)
) IN userspace1;

ALTER TABLE employees ADD VERSIONING
  USE HISTORY TABLE employees_history;

-- Query as of a specific time
SELECT * FROM employees
  FOR SYSTEM_TIME AS OF TIMESTAMP '2023-01-01 00:00:00';

• Added in 10.1.
• Supports system-time, application-time, and fully bi-temporal tables combining both.
• History table is created explicitly and attached with ALTER TABLE ... ADD VERSIONING USE HISTORY TABLE ...

-- Query as of a specific timestamp
SELECT * FROM employees
  AT(TIMESTAMP => '2023-01-01 00:00:00'::TIMESTAMP_TZ);

-- Query as of a relative offset (24 hours ago)
SELECT * FROM employees
  AT(OFFSET => -86400);

-- Query before a specific statement ID
SELECT * FROM employees
  BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

-- Restore a dropped table
UNDROP TABLE employees;

• Time Travel window: 1 day on Standard, up to 90 days on Enterprise.
• AT / BEFORE syntax, not SQL:2011. UNDROP TABLE recovers a dropped table within the window.
• Fail-safe extends recovery by 7 days after Time Travel expires, but is only accessible through Snowflake support.

-- Query as of 1 day ago using SQL:2011 syntax
SELECT * FROM mydataset.employees
  FOR SYSTEM_TIME AS OF
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);

-- Query as of a specific timestamp
SELECT * FROM mydataset.employees
  FOR SYSTEM_TIME AS OF '2023-01-01 00:00:00 UTC';

• FOR SYSTEM_TIME AS OF is enabled on every standard table with no setup.
• Retention is 2–7 days per table (default 7).
• RESTORE TABLE copies historical data back into a new or existing table.