CODEWITHSUNDEEP
snowflake-cloud-data-platform
Neeraj Kumar
Neeraj Kumar

Reputation: 245

Timestamp issue in Time Travel feature in Snowflake

I am new to SnowFlake. I created a table in Snowflake. Below are the details.

# Created @ 2020-01-07 21:11:20.810 -0800
CREATE TABLE employee2(
emp_id INT,
f_name STRING,
l_name STRING,
dept STRING);

When I am trying to get time travel data using below command, it gives me error.

Query:

select * from employee2 at(timestamp => '2020-01-07 21:12:20.810 -0800'::timestamp);

Error: Time travel data is not available for table EMPLOYEE2. The requested time is either beyond the allowed time travel period or before the object creation time.

Although the following command works well.

SELECT * FROM employee2 at(offset => -60 * 2);

Any hep on this would be much appreciated.

Regards, Neeraj

Upvotes: 4

Views: 5471

Answers (2)

Hans Henrik Eriksen
Hans Henrik Eriksen

Reputation: 2880

If the timestamp is in the local timezone, try to cast to the TIMESTAMP_LTZ datatype instead:

select * from employee2 at(timestamp => '2020-01-07 21:12:20.810 -0800'::timestamp_ltz);

Upvotes: 3

H Roy
H Roy

Reputation: 635

The duration for the Time travel feature varies from account to account. You need to check what kind of account you have created (Standard/Enterprise etc). The retention policy is 1 day for the standard version and 90 days for the enterprise version.

I also suggest setting the timezone before you play with this feature for better accuracy

ALTER SESSION  SET TIMEZONE = 'UTC'

and then trying running this query. Minor alteration at your millisecond part.

select * from employee2 before(timestamp => '2020-01-07 21:12:20.810'::timestamp);

The AT clause is used for offset so when you are using timestamp or any field, use the BEFORE keyword. 

--time travel 2 min ago
SELECT * FROM employee2 at(offset => -60 * 2);

Hope this helps, if not, please share your comment and error with a screenshot.

Upvotes: 4

Related Questions