Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

to_sql method turns datetime64 index to time zone aware in postgres #23510

Closed
MosheVai opened this issue Nov 5, 2018 · 5 comments

Comments

Projects
None yet
4 participants
@MosheVai
Copy link

commented Nov 5, 2018

I have a pandas Dataframe with an index which is of type datetime64[ns].
when I use the to_sql method i expect the index to be created as a timestamp postgres column, however it is creates a timestamptz column.

Note that when the datetime64[ns] column is not an index but rather a normal column, this doesn't happen.

pandas 0.23.4, psycopg2 2.7.4, sqlalchemy 1.2.7, PostgreSQL 9.6.6


example

dates = pd.date_range('2018-01-01', periods=5, freq='6h')
df_test = pd.DataFrame({'nums': range(5)}, index=dates)
nums
2018-01-01 00:00:00 0
2018-01-01 06:00:00 1
2018-01-01 12:00:00 2

inserting to postgres

df_test.to_sql('foo_table',postgres_uri,schema='data_test',index_label='info_date')

when reading from the database I get the index with different type

df_db = pd.read_sql_table('foo_table',postgres_uri,schema='data_test',index_col='info_date')
nums
info_date
2018-01-01 00:00:00+00:00 0
2018-01-01 06:00:00+00:00 1
2018-01-01 12:00:00+00:00 2
As you can see `info_date` is of type `datetime64[ns, UTC]`

not sure if it's a sqlalchemy or pandas question.
reported because of my question on SO

@TomAugspurger

This comment has been minimized.

Copy link
Contributor

commented Nov 5, 2018

cc @mroeschke is this changed by your tz-support PR?

@TomAugspurger TomAugspurger added the IO SQL label Nov 5, 2018

@mroeschke

This comment has been minimized.

Copy link
Member

commented Nov 6, 2018

Interesting. I don't think my tz-support would impact this. I can double check when i get a chance to reproduce.

@mroeschke

This comment has been minimized.

Copy link
Member

commented Nov 6, 2018

@MosheVai what is the 'data_test' schema you defined?

@MosheVai

This comment has been minimized.

Copy link
Author

commented Nov 6, 2018

@mroeschke it's a normal schema, I just didn't want to create a table in my public schema.

@mroeschke

This comment has been minimized.

Copy link
Member

commented Nov 7, 2018

I was able to confirm this behavior on master. I've included a fix in #22654 since it was a simple fix to handle.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.