snowflakedb / snowflakedb/snowflake-connector-python

SNOW-743104: Show a warning when fetching timestamp_tz columns to pandas

Open
#1,433 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

feature status-triage_done triaged
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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.