Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
608 views
in Technique[技术] by (71.8m points)

timestamp with timezone - Easiest way to convert timestamp_ntz to timestamp_tz in Snowflake database

I have a Timestamp_NTZ column where I know the data are in Central (America/Chicago) timezone. How do I convert it into Timestamp_TZ format during select.

I couldn't just convert to varchar and add timezone offset and back to timestamp_tz because of different offset during daylight saving.

I found following two approaches but they involves more code/typing. Looking for elegant solution.

  1. Alter session to the required timezone and use TO_TIMESTAMP_TZ() function. The drawback of this approach is that its two separate query and it cannot be used in stored procedure with run as Owner.

    alter session set timezone = 'America/Chicago';
    select TO_TIMESTAMP_TZ(column_name) from table_name;
    
  2. Use TIMESTAMP_TZ_FROM_PARTS function. This involves more typing and looks complicated.

    select TIMESTAMP_TZ_FROM_PARTS(Year(column_name), Month(column_name), Day(column_name), Hour(column_name), Minute(column_name), Second(column_name), 0, 'America/Chicago') from table_name;
    

Is there a simple way to do this in snowflake, something like:

select TO_TIMESTAMP_TZ(column_name, 'America/Chicago') from table_name;

question from:https://stackoverflow.com/questions/65895131/easiest-way-to-convert-timestamp-ntz-to-timestamp-tz-in-snowflake-database

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You should be able to use the CONVERT_TIMEZONE for this. The 2 argument version Doesn't expect TIMESTAMP_TZ as you mentioned in your comment. You can see below that I'm passing in a TIMESTAMP_NTZ for demonstration purposes but you could pass in a string like 2020-01-27T08:00:00Z too if you want:

select CONVERT_TIMEZONE( 'America/Chicago' , TO_TIMESTAMP_NTZ('2020-01-27T08:00:00Z'));

The above returns

2021-01-26 02:00:00.000000000 -06:00


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...