CODEWITHSUNDEEP
pythonpandastimezonesnowflake-cloud-data-platformread-sql
Ezer K
Ezer K

Reputation: 3739

snowflake convert_timezone does not work in pandas read sql

convert_timezone works for me in the snowflake console:

select convert_timezone('America/Los_Angeles', '2022-05-25T14:27:30Z'::TIMESTAMP_TZ)

gives:

2022-05-25T07:27:30-07:00

but it does not work in pandas read sql:

pd.read_sql("select convert_timezone('America/Los_Angeles', '2022-05-25T14:27:30Z'::TIMESTAMP_TZ)", engine).values

gives:

array([[Timestamp('2022-05-25 21:27:30+0000', tz='UTC')]], dtype=object)

any ideas how to fix this?

Upvotes: 0

Views: 553

Answers (1)

Alexander Klimenko
Alexander Klimenko

Reputation: 1695

When pandas receives TIMESTAMPs values from Snowflake it converts them to datetime64[ns]. It feels like in your case pandas converts the timestamp to UTC.

I suggest applying a conversion on the values in the dataframe using a series of methods like

.tz_localize("UTC").tz_convert("America/Los_Angeles"))

You can make sure that Snowflake generates the expected timestamp by going to the UI -> History and checking your query generated by Python Connector. In the query result section, you will be able to see the actual timestamp returned to pandas, highly likely it will be the expected one. Therefore, it should be adjusted on pandas.

Upvotes: 1

Related Questions