How to convert UNIX epoch days to DATE in Snowflake?

user_3255021 0
Viewed 1506

I have some values which correspond to the number of days since Epoch and want to store them in DATE column in Snowflake.

Example: 33057

Corresponds to Sunday, July 4, 2060

Would be really helpful if someone can point me to the correct Snowflake documentation(if available out of the box) for such

snowflake-cloud-data-platform sql
2 Answers

I think this will work:

select dateadd(day, <your number> - 33057, '2060-07-04')

So, for the specific conversion of "33057", this would be:

select dateadd(day, 33057 - 33057, '2060-07-04')

But it should work for any integer.

user_11440350

TO_TIMESTAMP_x(numeric_expr) is what your looking for. And if you only want the date part I would then truncate via ::DATE

numeric_expr A number of seconds (if scale = 0 or is absent) or fractions of a second (e.g. milliseconds or nanoseconds) since the start of the Unix epoch (1970-01-01 00:00:00 UTC). If a non-integer decimal expression is input, the scale of the result is inherited.

if you have milliseconds you can use the TO_TIMESTAMP_x(numeric_expr, scale) form

select to_timestamp(12334567) as t_from_s
  ,to_timestamp(12334567000, 3) as t_from_ms;

gives

T_FROM_S                   T_FROM_MS
1970-05-23 18:16:07.000    1970-05-23 18:16:07.000

We use the _NTZ variant for all our usages, as we have all data in UTC from the servers.

user_439920
Related