CODEWITHSUNDEEP
snowflake-cloud-data-platform
Rajib Deb
Rajib Deb

Reputation: 1774

Is there a built-in function to convert time zones

We are loading data from Hana to Snowflake. In Hana, we have time stored in different formats like CET, UTC+3, UTC+4, BRAZIL,AUSACT etc. In hana, we have a function that can convert the timestamp into these various time zones. How do we do it in Snowflake?

Upvotes: 0

Views: 382

Answers (2)

Rajib Deb
Rajib Deb

Reputation: 1774

The process I followed is as below

  1. Create a table to map the HANA and SNOWFLAKE format
CREATE OR REPLACE TABLE HANA_SF_CONVERT(HANA_FORMAT VARCHAR,SF_FORMAT VARCHAR);  

insert into HANA_SF_CONVERT(HANA_FORMAT,SF_FORMAT) VALUES('CET','Africa/Ceuta');
insert into HANA_SF_CONVERT(HANA_FORMAT,SF_FORMAT) VALUES('AUSACT','Australia/ACT');
  1. Created a function as below
CREATE OR REPLACE function hana_sf_convert(hana_fmt string,sf_tz string,date_val timestamp) 
    returns timestamp
    as 
    $$
        select convert_timezone((select SF_FORMAT from 
        HANA_SF_CONVERT where HANA_FORMAT = hana_fmt ),sf_tz ,date_val )
    $$
    ;

Finally, can query as below

select hana_sf_convert('CET','America/Los_Angeles',date_val::timestamp_ntz) as time, date_val from <table>;

Upvotes: 1

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26068

Greg nailed this.

  • To CONVERT_TIMEZONE - Yes!
  • To convert_time to/from the Timezones that you like to refer to - No

So that answers the question if there is built it functions.

Can you build your own function that uses SQL or JavaScript to covert from the reference/name space you have, to values Snowflake supports sure.

Upvotes: 0

Related Questions