CODEWITHSUNDEEP
python-3.xpandasoracle-databasedataframesqlalchemy
bzflag
bzflag

Reputation: 103

datetime64[ns] column on export is displayed as DATE datatype in Oracle DB

In my dataframe, I've a column called 'DateStamp' which shows today date as 2021-09-10 00:19:57.593350

final_df['DateStamp'] = pd.to_datetime('today')

However, when I export the dataframe to my Oracle Database, the DateStamp column is created with DATA_TYPE as DATE instead of timestamp

dtyp2 = {c:types.VARCHAR(final_df_toDB[c].str.len().max())
    ...:         for c in final_df_toDB.columns[final_df_toDB.dtypes == 'object'].tolist()}

final_df_toDB.to_sql('sample_table',engine_conn, if_exists='append', index=False, dtype=dtyp2)

and because of this, my Oracle Table only shows the Date Part of the timestamp. However, I'd like the column show entire datestamp

Upvotes: 0

Views: 441

Answers (1)

EdStevens
EdStevens

Reputation: 3872

In oracle, a DATE is actually date and time, down to the second. The display format of a DATE is under the control of the client, and most default to only presenting the date, but the time component is still there:

SQL> select sysdate default_format,
  2         to_char(sysdate,'dd-Mon-yyyy') dateonly,
  3         to_char(sysdate,'hh24:mi:ss') timeonly,
  4         to_char(sysdate,'dd-Mon-yyyy hh24:mi:ss') date_time
  5  from dual
  6  ;

DEFAULT_F DATEONLY             TIMEONLY DATE_TIME
--------- -------------------- -------- -----------------------------
09-SEP-21 09-Sep-2021          14:15:19 09-Sep-2021 14:15:19

1 row selected.

Upvotes: 3

Related Questions