How can write the datetime index into table with date type?

The dataframe contains an index whose dytpe is datetime64[s].

df
           ticker   close
2008-01-28   aacg  0.7380
2008-01-29   aacg  0.6797
2008-01-30   aacg  0.6603
2008-01-31   aacg  0.7418
2008-02-01   aacg  0.7387

Write the df into table quote:

df.to_sql('quote',con=engine,index=True)

Show records in postgresql:

select * from quote;

        index        | ticker | close  
---------------------+--------+--------
 2008-01-28 00:00:00 | aacg   |  0.738
 2008-01-29 00:00:00 | aacg   | 0.6797
 2008-01-30 00:00:00 | aacg   | 0.6603
 2008-01-31 00:00:00 | aacg   | 0.7418
 2008-02-01 00:00:00 | aacg   | 0.7387

Show table structure:

  \d quote
                         Table "public.quote"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 index  | timestamp without time zone |           |          | 
 ticker | text                        |           |          | 
 close  | double precision            |           |          | 
Indexes:
    "ix_quote_index" btree (index)

How can make the records written as below format and index is the format of date?

  index      | ticker | close  
------------+--------+--------
 2008-01-28  | aacg   |  0.738
 2008-01-29  | aacg   | 0.6797
 2008-01-30  | aacg   | 0.6603
 2008-01-31  | aacg   | 0.7418
 2008-02-01  | aacg   | 0.7387

If i convert the df.index into string with df.index.astype('str'),the index type in database would be text when to_sql,not expected!

>Solution :

Alter the df timestamp column to date:

# Convert index to date data type
df.index = df.index.date

df.to_sql('quote', con=engine, index=True, index_label='idx_quote_index')

Or, in Postgres, alter the column to date:

ALTER TABLE quote 
ALTER COLUMN index TYPE date 
USING index::date;

I would not recommend "index" as a column name in any SQL db it is a reserved keyword in SQL, so you would need to enclose it in double quotes whenever you reference it in a query. e.g:

SELECT "index", ticker, close
FROM quote;

So, in addition to changing the data type of that df column, also consider changing its name.

Leave a Reply