CODEWITHSUNDEEP
snowflake-cloud-data-platform
Pbd
Pbd

Reputation: 1319

Update the data type of a string column to timestamp in snowflake

I have a table with a column where timestamps are actually in a string format. I want to convert the datatype of this column to timestamp.

I have tried the following successfully.

create or replace table xyz as
select
    *,
    to_timestamp(my_time_column, 'YYYY-MM-DD HH24:MI:SS.FF UTC') as _my_time_column
from xyz
);

alter table xyz drop column my_time_column;

alter table xyz rename column _my_time_column to my_time_column;

This works well, but I find it too verbose. Is a better option available?

Upvotes: 1

Views: 1536

Answers (1)

Pbd
Pbd

Reputation: 1319

This is a good solution. Recently snowflake released new sql syntax that supports exclude & rename functionality.

You can hence try this technique:

  1. Create a column with correct data: to_timestamp(my_time_column, 'YYYY-MM-DD HH24:MI:SS.FF UTC') and assign some unique name -- _my_time_column
  2. Now exclude the original column with varchar type & rename the above column _my_time_column to the actual column.
  3. Wrap this entire query into a CTAS.

Sample solution:

create or replace table xyz as
select * exclude my_time_column rename _my_time_column as my_time_column from (
    select
        *,
        to_timestamp(my_time_column, 'YYYY-MM-DD HH24:MI:SS.FF UTC') as _my_time_column
    from xyz
);

You can read more about these new syntax in following docs:

  1. Snowflake reference.
  2. Blog post.
  3. Detailed usage.

Upvotes: 1

Related Questions