read_sql_query does not convert invalid dates to NaT

[jorisvandenbossche]

From: http://stackoverflow.com/questions/27908071/pandas-interprets-timestamp-without-timezones-columns-as-different-types/27960779#27960779

Using:

df = pd.DataFrame({'col1':[0,1], 'col2':pd.date_range('2012-01-01', periods=2, freq='1h')})
df.to_sql('test_invalid_date', engine_postgres, if_exists='replace', index=False)
engine_postgres.execute("INSERT INTO test_invalid_date VALUES (2, '3012-01-01 00:00:00');")

it gives a different handling of the invalid date depending on using read_sql_table or read_sql_query:

In [34]: df1 = pd.read_sql_table('test_invalid_date', engine_postgres)

In [35]: df1
Out[35]:
   col1                col2
0     0 2012-01-01 00:00:00
1     1 2012-01-01 01:00:00
2     2                 NaT

In [36]: df1.dtypes
Out[36]:
col1             int64
col2    datetime64[ns]
dtype: object

In [37]: df2 = pd.read_sql_query('SELECT * FROM test_invalid_date', engine_postgres)

In [38]: df2
Out[38]:
   col1                 col2
0     0  2012-01-01 00:00:00
1     1  2012-01-01 01:00:00
2     2  3012-01-01 00:00:00

In [39]: df2.dtypes
Out[39]:
col1     int64
col2    object
dtype: object

In [40]: df2.col2.values
Out[40]:
array([datetime.datetime(2012, 1, 1, 0, 0),
       datetime.datetime(2012, 1, 1, 1, 0),
       datetime.datetime(3012, 1, 1, 0, 0)], dtype=object)

[jorisvandenbossche]

In the first place, it is an inconsistency between the two functions.
But on a second thought, I don't know if we can do something about this. As read_sql_query really just gets the result of the query and puts it in a dataframe. We don't do anything with the types. Normally the datetime.datetime values are coerced automatically to datetime64 when put in a dataframe, but not when they are not convertable.