CODEWITHSUNDEEP
datetimesnowflake-cloud-data-platform
gopinath kolanchi
gopinath kolanchi

Reputation: 180

Snowflake GMT String to Date Conversion

We receive a string '2019-11-30T18:00:00GMT-06:00' in the JSON file and this need to be converted to timestamp to load into the timestamp column in the snowflake. I tried multiple options convert_timezone,to_timestamp etc, however in vain, Can you please let me know how i represent this string (2019-11-30T18:00:00GMT-06:00) in data format for comversion.

Thanks !

Upvotes: 0

Views: 1203

Answers (3)

Justin Grilli
Justin Grilli

Reputation: 1

Just to add on to the accepted answer, assuming the timezone could be -/+ GMT, you can forgo the - in the format:

SELECT TO_TIMESTAMP($1,'YYYY-MM-DDTHH:MI:SSGMTTZH:TZM')
FROM VALUES ('2019-11-30T18:00:00GMT-06:00'), ('2019-11-30T18:00:00GMT+06:00');

Snowflake will include -/+ in the use of TZH:TZM. (RE docs)

If the timezone is important for you to retain, you should consider using TO_TIMESTAMP_TZ instead:

SELECT TO_TIMESTAMP_TZ($1,'YYYY-MM-DDTHH:MI:SSGMTTZH:TZM')
FROM VALUES ('2019-11-30T18:00:00GMT-06:00'), ('2019-11-30T18:00:00GMT+06:00');

Upvotes: 0

Mike Walton
Mike Walton

Reputation: 7369

Leveraging a more Snowflake way to do this, you'd want to run something like this:

SELECT TO_TIMESTAMP('2019-11-30T18:00:00GMT-06:00','YYYY-MM-DDTHH:MI:SSGMT-TZH:TZM');

The output of this will be the date/time of your account default along with the timezone offset that goes along with that.

Upvotes: 1

Rajib Deb
Rajib Deb

Reputation: 1774

Please try the below mentioned approach

if your table is create as below

CREATE TABLE TIMESTAMP_TEST(DATE TIMESTAMP);

Insert the value as

INSERT INTO TIMESTAMP_TEST SELECT REPLACE(REPLACE('2019-11-30T18:00:00GMT-06:00','GMT'),'T',' ') FROM DUAL

Thanks

Upvotes: 0

Related Questions