How to deal with 'out-of-bounds' error for timestamp in the method 'pandas.read_sql()'

Question

Environment :

pandas 0.20.3
python 2.7.13

Objective and Problem :

I'm using method pandas.read_sql() to retrieve the table from SQL that also contains values of invalidated timestamp (9999-12-30 23:00:00). But, the line of code throws the error 'Out of bounds nanosecond timestamp : 9999-12-30 23:00:00'.

How do i deal with parsing the timestamp in this case?

Background :

I have checked the pandas documentation for the solution. However, it seems to have no relevant support to configure the timestamp parser.

Code Snippet :

import pyodbc as pyo
import pandas as pd
db_conn = pyo.connect('DSN=MySQL Connection 3;',autocommit=True)
tbl_df = pd.read_sql('select * from orders',db_conn)

I expect to either parse the cells consisting of invalidated timestamps or to avoid parsing timestamps by default.

Can't provide a concise answer because there is not an MCVE in the question. https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — Rich Andrews, Mar 29 '19 at 18:02

score 0 · Answer 1 · answered Mar 29 '19 at 18:08

In 0.20.3 pandas.read_sql() has parse_dates : list or dict, default: None

So it would be unexpected that pandas is parsing the date found in your database.

Remove select * from orders to select col1, ..., datecol from orders and eliminate datecol. What happens?

Change datecol to UNIX_TIMESTAMP(STR_TO_DATE(datecol, '%M %d %Y %h:%i')) so that you can pull the data in as a non-datetime formate. From there, you can convert in Pandas.

How to deal with 'out-of-bounds' error for timestamp in the method 'pandas.read_sql()'

Environment :

Objective and Problem :

Background :

Code Snippet :

1 Answers1