snowflakedb / snowflakedb/snowflake-connector-python
SNOW-743104: Show a warning when fetching timestamp_tz columns to pandas
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 730
- Forks
- 574
- Avg merge
- 5h 45m
- Merged PRs (30d)
- 16
Description
What is the current behavior?
Reading a timestamp_tz column with fetch_pandas_all() always converts timezone to the timezone in session parameter TIMEZONE, which defaults to "America/Los_Angeles."
A quick reproducer:
import snowflake.connector
con = snowflake.connector.connect(...)
cursor = con.cursor()
cursor.execute("select '2017-01-01 12:00:00 +1100'::timestamp_tz time_utc_plus_11;")
pandas_result = cursor.fetch_pandas_all()
# prints dataframe with time in los angeles time zone:
# 2016-12-31 17:00:00-08:00
print(pandas_result)
# prints datetime64[ns, America/Los_Angeles]
print(pandas_result.dtypes[0])
# fetch without pandas shows time with 11-hour offset
# [(datetime.datetime(2017, 1, 1, 12, 0, tzinfo=pytz.FixedOffset(660)),)]
print(cursor.fetchall())
What is the desired behavior?
I want the snowflake connector to warn the user that the pandas time for a TIMESTAMP_TZ column is always shown in the timezone TIMEZONE.
How would this improve snowflake-connector-python?
I think Snowflake connector's behavior is justified because snowflake only stores the time and the offset in TIMESTAMP_TZ, not a particular region, so the connector can't distinguish between e.g. America/Los_Angeles and America/Tijuana. However, in this case, I expected cursor.fetch_pandas_all() to return a dataframe with the time in UTC + 11, so 2017-01-01 12:00:00+1100 , with dtype . Instead I got the time in America/Los_Angeles, and I was confused for a while. I finally figured out that 1) I was getting America/Los_Angeles because that was my TIMEZONE and 2) there was no way to map from "UTC time with offset" to pandas "UTC time with offset and timezone."
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the issue with cursor.fetch_pandas_all() and the TIMESTAMP_TZ query shown in the report, then trace the fetch_pandas_all() path. The work is done when users receive a warning explaining that pandas displays the column in the session TIMEZONE; verify the warning with the example and compare it with fetchall().
Written by the indexing model from the issue text.
Assessment
- Tech stack
- pandas, python, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100