to_sql method turns datetime64 index to time zone aware in postgres · Issue #23510 · pandas-dev/pandas (original) (raw)
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