Historical query

Vertica can run a query from a snapshot of the database taken at a specific date and time or at a specific epoch. The syntax is:

   AT TIME 'timestamp' SELECT...

   AT EPOCH epoch_number SELECT...

   AT EPOCH LATEST SELECT...

The command queries all data in the database up to and including the specified epoch or the epoch representing the specified date and time, without holding a lock or blocking write operations. The specified TIMESTAMP and epoch_number values must be greater than or equal to the Ancient History Mark epoch.

Historical queries, also known as snapshot queries, are useful because they access data in past epochs only. Historical queries do not need to hold table locks or block write operations because they do not return the absolute latest data. Their content is private to the transaction and valid only for the length of the transaction.

Historical queries behave in the same manner regardless of transaction isolation level. Historical queries observe only committed data, even excluding updates made by the current transaction, unless those updates are to a temporary table..

Be aware that there is only one snapshot of the logical schema. This means that any changes you make to the schema are reflected across all epochs. If, for example, you add a new column to a table and you specify a default value for the column, all historical epochs display the new column and its default value.