AT | BEFORE¶

Syntax¶

SELECT ...
FROM ...
  {
   AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> | STREAM => '<name>' } ) |
   BEFORE( STATEMENT => <id> )
  }
[ ... ]

Parameters¶

TIMESTAMP => timestamp

Specifies an exact date and time to use for Time Travel. The value must be explicitly cast to a TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, or TIMESTAMP_TZ data type.

If no explicit cast is specified, the timestamp in the AT clause is treated as a timestamp with the UTC time zone (equivalent to TIMESTAMP_NTZ). Using the TIMESTAMP data type for an explicit cast may also result in the value being treated as a TIMESTAMP_NTZ value. For details, see Date & time data types.

OFFSET => time_difference

Specifies the difference in seconds from the current time to use for Time Travel, in the form -N where N can be an integer or arithmetic expression (e.g. -120 is 120 seconds, -30*60 is 1800 seconds or 30 minutes).

STATEMENT => id

Specifies the query ID of a statement to use as the reference point for Time Travel. This parameter supports any statement of one of the following types:

• DML (e.g. INSERT, UPDATE, DELETE)

• TCL (BEGIN, COMMIT transaction)

• SELECT

The query ID must reference a query that has been executed within the last 14 days. If the query ID references a query over 14 days old, the following error is returned:

Error: statement <query_id> not found

To work around this limitation, use the timestamp for the referenced query.

STREAM => 'name'

Specifies the identifier (i.e. name) for an existing stream on the queried table or view. The current offset in the stream is used as the AT point in time for returning change data for the source object.

This keyword is supported only when creating a stream (using CREATE STREAM) or querying change data (using the CHANGES clause). For examples, see these topics.

Using the AT TIMESTAMP parameter¶

In the AT clause, you can specify the TIMESTAMP keyword followed by a string that represents a timestamp and an optional explicit cast to the TIMESTAMP, TIMESTAMP_TZ, TIMESTAMP_LTZ, or TIMESTAMP_NTZ data type. The following examples are all valid:

AT ( TIMESTAMP => '2024-06-05 12:30:00'::TIMESTAMP_LTZ )

AT ( TIMESTAMP => '2024-06-05 12:30:00'::TIMESTAMP )

AT ( TIMESTAMP => '2024-06-05 12:30:00' )

If no explicit cast is specified, the timestamp in the AT clause is treated as a timestamp with the UTC time zone (equivalent to TIMESTAMP_NTZ). Using the TIMESTAMP data type for an explicit cast may also result in the value being treated as a TIMESTAMP_NTZ value, as discussed in Date & time data types.

The explicit cast that you choose affects the results of Time Travel queries because timestamps are interpreted with respect to the current time zone for the session and the value of the TIMESTAMP_TYPE_MAPPING parameter. For more details about this behavior, see Querying Time Travel data in a session with a non-UTC time zone.

For example, you are running queries in a SQL session where the current time zone is America/Los_Angeles and TIMESTAMP_TYPE_MAPPING is set to TIMESTAMP_NTZ. Create a table and immediately insert two rows:

CREATE OR REPLACE TABLE tt1 (c1 INT, c2 INT);
INSERT INTO tt1 VALUES(1,2);
INSERT INTO tt1 VALUES(2,3);

Check the creation time of the table with a SHOW TABLES command:

SHOW TERSE TABLES LIKE 'tt1';

+-------------------------------+------+-------+---------------+----------------+
| created_on                    | name | kind  | database_name | schema_name    |
|-------------------------------+------+-------+---------------+----------------|
| 2024-06-05 15:25:35.557 -0700 | TT1  | TABLE | TRAVEL_DB     | TRAVEL_SCHEMA  |
+-------------------------------+------+-------+---------------+----------------+

Note the time zone offset in the created_on column. Five minutes later, insert another row:

INSERT INTO tt1 VALUES(3,4);

Now run the following Time Travel query, expecting it to return the first two rows:

SELECT * FROM tt1 at(TIMESTAMP => '2024-06-05 15:29:00'::TIMESTAMP);

000707 (02000): Time travel data is not available for table TT1. The requested time is either beyond the allowed time travel period or before the object creation time.

The query fails because the time zone of the session is UTC, and the explicit cast to TIMESTAMP honors that time zone. Therefore, the table is assumed to have been created after the specified timestamp. To solve this problem, run the query again with an explicit cast to TIMESTAMP_LTZ (local time zone):

SELECT * FROM tt1 at(TIMESTAMP => '2024-06-05 15:29:00'::TIMESTAMP_LTZ);

+----+----+
| C1 | C2 |
|----+----|
|  1 |  2 |
|  2 |  3 |
+----+----+

As expected, the query returns the first two rows that were inserted. Finally, run the same query but specify a slightly later timestamp:

SELECT * FROM tt1 at(TIMESTAMP => '2024-06-05 15:31:00'::TIMESTAMP_LTZ);

+----+----+
| C1 | C2 |
|----+----|
|  1 |  2 |
|  2 |  3 |
|  3 |  4 |
+----+----+

This query returns all three rows, given the later timestamp.

Using the BEFORE clause¶

The STATEMENT parameter in the BEFORE clause must refer to a query ID. The point in the past used by Time Travel is just before the statement for that query ID is completed rather than before the statement is started. If concurrent queries commit modifications to the data between the start and end of the statement, these changes are included in your results.

For example, the following statements are being executed on table my_table in parallel in two separate threads:

As a workaround, you can use a TIMESTAMP parameter that specifies a point in time just before the start of the statement.

Usage notes¶

• Data in Snowflake is identified by timestamps that can differ slightly from the exact value of system time.

• The value for TIMESTAMP or OFFSET must be a constant expression.

• The smallest time resolution for TIMESTAMP is milliseconds.

• If requested data is beyond the Time Travel retention period (default is 1 day), the statement fails.

  In addition, if the requested data is within the Time Travel retention period but no historical data is available (e.g. if the retention period was extended), the statement fails.

• If the specified Time Travel time is at or before the point in time when the object was created, the statement fails. See Using the AT TIMESTAMP parameter.

• When you access historical table data, the results include the columns, default values, etc. from the current definition of the table. The same applies to non-materialized views. For example, if you alter a table to add a column, querying for historical data before the point in time when the column was added returns results that include the new column.

• Historical data has the same access control requirements as current data. Any changes are applied retroactively.

• The AT and BEFORE clauses do not support selecting historical data from a CTE.

  For example, the following query is not supported:

  WITH mycte AS
    (SELECT mytable.* FROM mytable)
  SELECT * FROM mycte AT(TIMESTAMP => '2024-03-13 13:56:09.553 +0100'::TIMESTAMP_TZ);
  

  Copy

  However, these clauses are supported in a query in a WITH clause. For example, the following query is supported:

  WITH mycte AS
    (SELECT * FROM mytable AT(TIMESTAMP => '2024-03-13 13:56:09.553 +0100'::TIMESTAMP_TZ))
  SELECT * FROM mycte;
  

  Copy

• Time Travel queries against hybrid tables have the following limitations:

  • Only the TIMESTAMP parameter is supported in the AT clause. The OFFSET, STATEMENT, and STREAM parameters are not supported.

  • The value of the TIMESTAMP parameter must be the same for all tables that belong to the same database. If the tables belong to different databases, different TIMESTAMP values may be used.

  • The BEFORE clause is not supported.

• CREATE DATABASE … CLONE and CREATE SCHEMA … CLONE commands that use Time Travel and specify the time with the STATEMENT parameter return an error if any hybrid tables exist in the specified database. The error prompts you to run the command using the IGNORE HYBRID TABLES parameter. When you include this parameter, the command will create the cloned database or schema but skip any hybrid tables.

Troubleshooting¶

Time travel data is not available for table <tablename>

... AT(TIMESTAMP => '2018-07-27 12:00:00')               -- fails
... AT(TIMESTAMP => '2018-07-27 12:00:00'::TIMESTAMP)    -- succeeds

Examples¶

Select historical data from a table using a specific timestamp. In the first two examples, which use the TIMESTAMP parameter, my_table could be a standard table or a hybrid table. Subsitute a recent date, time, or timestamp that’s within the retention period.

SELECT * FROM my_table AT(TIMESTAMP => 'Wed, 26 Jun 2024 09:20:00 -0700'::TIMESTAMP_LTZ);

SELECT * FROM my_table AT(TIMESTAMP => TO_TIMESTAMP(1432669154242, 3));

Select historical data from a table as of 5 minutes ago:

SELECT * FROM my_table AT(OFFSET => -60*5) AS T WHERE T.flag = 'valid';

Select historical data from a table up to, but not including any changes made by the specified transaction:

SELECT * FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

Return the difference in table data resulting from the specified transaction:

SELECT oldt.* ,newt.*
  FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt
    FULL OUTER JOIN my_table AT(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt
    ON oldt.id = newt.id
  WHERE oldt.id IS NULL OR newt.id IS NULL;

The following example runs a Time Travel join query against two tables in the same database, one of which is a hybrid table. The same TIMESTAMP expression must be used for both tables. Subsitute a recent date, time, or timestamp that’s within the retention period.

SELECT *
  FROM db1.public.htt1
    AT(TIMESTAMP => '2024-06-05 17:50:00'::TIMESTAMP_LTZ) h
    JOIN db1.public.tt1
    AT(TIMESTAMP => '2024-06-05 17:50:00'::TIMESTAMP_LTZ) t
    ON h.c1=t.c1;