CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
abb
abb

Reputation: 382

How to convert UNIX epoch seconds to Timestamp in Snowflake?

I could not find any function in snowflake docs that can do this.

Upvotes: 4

Views: 20491

Answers (3)

Bruno Alexandro García Tejada
Bruno Alexandro García Tejada

Reputation: 141

Actually the function to_timestamp() in order to determine the units to use (seconds, milliseconds, microseconds, or nanoseconds), needs to receive a varchar instead of a bigint. So you just need to first cast the bigint to a varchar and you will be fine

select to_timestamp(31536000::varchar); --returns "1971-01-01" (correct)
select to_timestamp(31536000000::varchar); --returns "1971-01-01" (correct)
select to_timestamp(31536000000000::varchar); --returns "1971-01-01" (correct)
select to_timestamp(31536000000000000::varchar); --returns "1971-01-01" (correct)

Upvotes: 6

sethberickson
sethberickson

Reputation: 81

Unfortunately I don't think there is a perfect solution for this issue. The Snowflake docs do say that the to_timestamp() function supports epoch seconds, microseconds, and nanoseconds, however their own example using the number 31536000000000000 does not even work.

select to_timestamp(31536000000000000); -- returns "Invalid Date" (incorrect)

The number of digits your epoch number has will vary by its origin source. What I found helpful was using a tool like epoch converter to input the full epoch number and determine what your date should be. Then try to get to that date in Snowflake using some manipulation. To get the real date for the example above:

select to_timestamp(left(31536000000000000, 11)); -- returns "1971-01-01" (correct)

You may notice that this is not set in stone. Adding or removing the number of digits you keep in your to_timestamp function will completely change the output, so you may need to add or remove numbers to get the date you are looking for. For example, the number 1418419324000000 should return date "2014-12-12"...

select to_timestamp(1418419324000000); -- returns "Invalid Date" (incorrect)
select to_timestamp(left(1418419324000000, 11)); -- returns "2419-06-24" (incorrect)
select to_timestamp(left(1418419324000000, 10)); -- returns "2014-12-12" (correct)

I had to play around with how many characters I input to get to where I needed to be. It's definitely a hack, but it's a simple solution to get there.

Upvotes: 1

martin clayton
martin clayton

Reputation: 78105

If I understand what you mean correctly it appears to be:

TO_TIMESTAMP( epoch_sec )

This is the reference. There's variations for time zone support too.

Upvotes: 6

Related Questions