BUG: `read_sql()` does not handle `date` nor `time` datatypes correctly when using pyarrow (but works for `datetime`)

[fdcastel]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import sqlalchemy as sa
import pandas as pd

e = sa.create_engine(db_uri)    # Any Microsoft SQL Server uri
with e.connect() as conn:
    # SQL Server query -- If using another RDBMS, please adjust.
    q = "SELECT CAST(CURRENT_TIMESTAMP as date) D, CAST(CURRENT_TIMESTAMP as time) T, CURRENT_TIMESTAMP DT"

    # Execute query with SQLAlchemy
    r = conn.exec_driver_sql(q)

    # Result metadata has the correct data types for each column
    print(r.cursor.description)
    # (('D', <class 'datetime.date'>, None, 10, 10, 0, True), ('T', <class 'datetime.time'>, None, 16, 16, 7, True), ('DT', <class 'datetime.datetime'>, None, 23, 23, 3, False))

    # Execute query with read_sql()
    df = pd.read_sql(sql=q, con=db_uri, dtype_backend="pyarrow")

    # Dataframe metadata has the correct data type only for the last column (datetime).
    print(df.dtypes)
    # D            string[pyarrow]
    # T            string[pyarrow]
    # DT    timestamp[ns][pyarrow]

Issue Description

When used with pyarrow backend Pandas read_sql() does not handle date nor time datatypes correctly.

But it works for datetime datatypes.

The above example uses Microsoft SQL Server but can be reproduced in any other database.

Expected Behavior

print(df.dtypes)
# D        date64[ns][pyarrow]
# T        time64[ns][pyarrow]
# DT    timestamp[ns][pyarrow]

Installed Versions

INSTALLED VERSIONS

commit : a671b5a
python : 3.11.7.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.20348
machine : AMD64
processor : Intel64 Family 6 Model 183 Stepping 1, GenuineIntel
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : pt_BR.cp1252

pandas : 2.1.4
numpy : 1.26.2
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 69.0.2
pip : 23.3.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader : None
bs4 : None
bottleneck : 1.3.7
dataframe-api-compat: None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : 0.58.1
numexpr : 2.8.8
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 14.0.1
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : 2.0.23
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

[paridhisharma24]

can I work on this

[enigmazack]

I tested this bug using MySQL and MySQLdb as the client. When directly fetching data via the SQLAlchemy engine, DATE/TIME/DATETIME types are respectively mapped to datetime.date, datetime.timedelta, and datetime.datetime. The generic method provided by pandas then converts datetime.timedelta and datetime.datetime to numpy.timedelta64 and numpy.datetime64, while leaving datetime.date unchanged.

Upon setting dtype_backend='pyarrow', pandas proceeds with further data type conversions based on the aforementioned process. This conversion takes place within the function pandas.core.internals.construction.convert_object_array.

By default, columns with dtype == np.dtype("O") are processed as strings. Consequently, datetime.date is treated as a string.

for dtype_backend in ["numpy_nullable", "pyarrow", lib.no_default]:
    df = pd.read_sql(
        "SELECT * FROM test_table", con=engine, dtype_backend=dtype_backend
    )
    print(dtype_backend)
    print(df.dtypes)

numpy_nullable
id                           Int64
date_column         string[python]
time_column        timedelta64[ns]
datetime_column     datetime64[ns]
message             string[python]
number                       Int64
score                      Float64
dtype: object
pyarrow
id                         int64[pyarrow]
date_column               string[pyarrow]
time_column         duration[ns][pyarrow]
datetime_column    timestamp[ns][pyarrow]
message                   string[pyarrow]
number                     int64[pyarrow]
score                     double[pyarrow]
dtype: object
_NoDefault.no_default
id                           int64
date_column                 object
time_column        timedelta64[ns]
datetime_column     datetime64[ns]
message                     object
number                     float64
score                      float64
dtype: object

A possible solution is:

df = pd.read_sql(
    "SELECT * FROM test_table",
    con=engine,
    dtype_backend="pyarrow",
    dtype={"date_column": "date64[ms][pyarrow]"},
)

Additionally, note that the format for time_column is duration[ns][pyarrow], not time64[ns][pyarrow].