CODEWITHSUNDEEP
snowflake-cloud-data-platform
Kar
Kar

Reputation: 1014

Snowflake:Epoch to Timestamp

Requirement: Convert Epoch to Timestamp

Issue: It is converting into local timezone

When I have checked manually at online it is as below

enter image description here enter image description here

When I tried in Snowflake it is as enter image description here

Expected output is 2017-12-15 09:21:15 | 2017-12-19 14:21:59

Upvotes: 1

Views: 3124

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

The easiest way to get a timestamp from epoch is TO_TIMESTAMP(). But if you also want to work with timezones, you'll need a timestamp with timezone. For that, use TO_TIMESTAMP_TZ().

SELECT TO_TIMESTAMP(1513347675), TO_TIMESTAMP(1513711319)
 , TO_TIMESTAMP_TZ(1513347675), TO_TIMESTAMP_TZ(1513711319)

# 17-12-15 14:21
# 17-12-19 19:21
# 17-12-15 09:21
# 17-12-19 14:21

--

Complete example:

ALTER SESSION SET TIMESTAMP_TZ_OUTPUT_FORMAT = 'YY-MM-DD HH24:MI TZHTZM';

ALTER SESSION SET TIMEZONE = 'Etc/GMT';
SELECT TO_TIMESTAMP_TZ(1513347675);
-- 17-12-15 14:21 +0000

ALTER SESSION SET TIMEZONE = 'US/Eastern';
SELECT TO_TIMESTAMP_TZ(1513347675);
-- 17-12-15 09:21 -0500

ALTER SESSION SET TIMEZONE = 'US/Pacific';
SELECT TO_TIMESTAMP_TZ(1513347675);
-- 17-12-15 06:21 -0800

Upvotes: 2

Related Questions