Convert Unix epoch time into Snowflake Date

Post author:Vithal S
Post last modified:September 7, 2020
Post category:Snowflake
Reading time:3 mins read

Many relational databases such as PostgreSQL support functions to convert Unix epoch time into a date or timestamp value. But, there are no built-in functions available in Snowflake that you can use to convert epoch time. You will have to use an alternate approach to convert epoch format. In this article, we will check how to convert Unix epoch time into the Snowflake date with some examples.

Convert Unix epoch time into Snowflake Date

The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970.

To_timestamp Function to Convert epoch date

Though you can use Snowflake cloud data warehouse to_timestamp function to convert few epoch date formats. But, the same will not work with all epoch values. You may get incorrect result when you try to convert epoch timestamp values.

For example, consider following example to convert 12334567 value to timestamp.

Snuser#COMPUTE_WH@DEMO_DB.PUBLIC>select to_timestamp(12334567);
+-------------------------+
| TO_TIMESTAMP(12334567)  |
|-------------------------|
| 1970-05-23 18:16:07.000 |
+-------------------------+
1 Row(s) produced. Time Elapsed: 7.458s

But, if you try to covert 1464800406459 epoch, you will not incorrect result.

Snuser#COMPUTE_WH@DEMO_DB.PUBLIC>select to_timestamp(1464800406459);
+-----------------------------+
| TO_TIMESTAMP(1464800406459) |
|-----------------------------|
| 48387-09-05 09:47:39.000    |
+-----------------------------+
1 Row(s) produced. Time Elapsed: 2.396s

But, as a workaround, you can just add the number of seconds to January 1, 1970 and return a timestamp.

Use Dateadd Function to Convert epoch to Date or Timestamp

The simple solution is to use the Snowflake dateadd date function to convert epoch to date or timestamp function.

For example,

Snuser#COMPUTE_WH@DEMO_DB.PUBLIC>SELECT dateadd('ms',timestamp_col,'1970-01-01')
from ( select 1464800406459 as timestamp_col) as a;
+------------------------------------------+
| DATEADD('MS',TIMESTAMP_COL,'1970-01-01') |
|------------------------------------------|
| 2016-06-01 17:00:06.459                  |
+------------------------------------------+

Here, we know that the epoch timestamp is the elapsed seconds since 1970-01-01. we will add an epoch timestamp to 1970-01-01 and we will get required date or timestamp format.