Error with pandas.Dataframe.to_sql -> PostgreSQL database due to timestamp datatype mismatch-CodePudding

I'm having trouble with inserting rows to my PostgresSQL table using the pandas.DataFrame.to_sql() method. Any help is appreciated, thank you!

Sample dataframe:

index | m_date     | ticker | close
0     | 1514937600 | AMD    | 11.55

When I run the following code:

price_data.to_sql(
    'prices',
    db_conn,
    if_exists='append',
    index=False,
    dtype={
        'close': sqlalchemy.types.FLOAT,
        'm_date': sqlalchemy.types.TIMESTAMP,
        'ticker': sqlalchemy.types.VARCHAR,
    }
)

I get this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) column "m_date" is of type timestamp without time zone but expression is of type integer
LINE 1: ..._indicator, market_capitalization) VALUES (11.55, 1514937600...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

Could you please help me identifying what I am doing wrong with casting the dtypes or how I can change the datatype for the entire column? I've tried the .astype() method as well:

price_data.astype({'m_date': sqlalchemy.types.TIMESTAMP})

But then I get the following error:

TypeError: dtype '<class 'sqlalchemy.sql.sqltypes.TIMESTAMP'>' not understood

CodePudding user response：

1514937600 is not a timestamp it is an epoch.

This can be seen with:

select to_timestamp(1514937600); 

2018-01-02 16:00:00-08

You will need to convert the value to a Python datetime before sending it to database. Something like:

import pandas as pd

pd.to_datetime(1514937600, unit='s') 

Timestamp('2018-01-03 00:00:00')

See Pandas to_datetime.