pd.read_sql formatting issue with timestamp

Question

I am using the below to extract data from database using cx_Oracle.connect for connection. I am having issue when the field I'm trying to extract from database is of datatype TIMESTAMP(6).

Value retrieved is 1625236451324000000 instead of 02-JUL-21 02.54.05.569000 PM

df_ora = pd.read_sql(sql_query_lpi, con=md_connection)
df_list=df_ora.values.tolist()
for columnname in df_list:
    run_info = dict()
    run_info['UPDATE_TS'] = columnname[0]

Any special formatting required in pandas to handle this ?

Thank you for any help/suggestion.

The integer value appears to be an epoch time. Try this answer. — s3dev
– s3dev, Commented Jul 3, 2021 at 19:16

Umar.H · Accepted Answer · 2021-07-04 19:35:08Z

1

you need to pass in the unit argument when casting it to a datetime,

pd.to_datetime(your_col, unit='ns')

In [4]: pd.Timestamp(1625236451324000000, unit='ns')
Out[4]: Timestamp('2021-07-02 14:34:11.324000')

import pandas as pd
df = pd.DataFrame({'col' : [1625236451324000000]})
df['date_col'] = pd.to_datetime(df['col'], unit='ns')

print(df)
                   col                date_col
0  1625236451324000000 2021-07-02 14:34:11.324

Edit.

If you need to preserve a format then use .dt.srftime, note this will turn your timestamp into a string.

 df['date_col_sql'] = pd.to_datetime(df['col'], unit='ns')\
                        .dt.strftime('%d-%b-%y %I.%M.%S.%f')


                   col                date_col               date_col_sql
0  1625236451324000000 2021-07-02 14:34:11.324  02-Jul-21 02.34.11.324000

edited Jul 4, 2021 at 19:35

answered Jul 3, 2021 at 19:47

Umar.H

23.1k7 gold badges50 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pats4u Over a year ago

Thank you Umar . I would like to preserve the format of timestamp format i.e., 02-JUL-21 02.54.05.569000 PM in the above example so I can use that in query again. Is there a way to do this ? Thank you.

Umar.H Over a year ago

@pats4u see edit, sorry I've not been on until right now, hope this helps.

Collectives™ on Stack Overflow

pd.read_sql formatting issue with timestamp

1 Answer 1

Edit.

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit.

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related