BUG: pyarrow engine timestamp conversion fails when doing pd.read_csv

[pradipneupane]

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 pandas as pd
import io

csv_data = """time
2026-04-20 10:00:00
2026-04-20 10:05:00
2026-04-20 10:10:00
"""

df = pd.read_csv(
    io.StringIO(csv_data),
    engine="pyarrow",
)

df['time'] = pd.to_datetime(df['time'])
df['time'] = df['time'].astype('int64')
df['time'] = pd.to_datetime(df['time'], unit='ns')
print(df)

Issue Description

from above example; it prints but if engine is set to python, then it works correctly:

                           time
0 1970-01-01 00:00:01.776679200
1 1970-01-01 00:00:01.776679500
2 1970-01-01 00:00:01.776679800

Expected Behavior

it should give:

                 time
0 2026-04-20 10:00:00
1 2026-04-20 10:05:00
2 2026-04-20 10:10:00

Installed Versions

it still exist in latest version of pandas and in 2.3.3

[jorisvandenbossche]

I don't think there is a bug here, but the different engines do have some different behaviour here, and the astype(int64) conversion is very brittle, since the timestamp column can have different units, giving different results because of the behaviour difference in the engine.

First, on the read_csv part:

• The pyarrow engine auto-detects timestamp columns in read_csv (and will in this case return a datetime64[s] dtype column)
• While the python engine does not (it keeps them as strings unless explicitly specified through parse_dates).

The subsequent pd.to_datetime(df['time']) will still parse the strings in case of the python engine, but that uses pandas' default us resolution since pandas 3.0 (it returns a datetime64[us] dtype column, for older versions it will use nanoseconds).

Then, the result of df['time'].astype('int64') is unit-dependent. If you want to do such a conversion, you always have to verify the unit first, to ensure you have the correct integer values as you want them.

[pradipneupane]

@jorisvandenbossche thank you for the reply. I was using above code for interpolation of the time; so changing it to integer and then do the interpolation of it and then changing it back to datetime. If pyarrow is not handling properly for that int conversion then should it complain already here ? df['time'] = df['time'].astype('int64') or if the int64 conversion is correct, then when converting back to to_datetime, may be it should complain ? Some proper error message would be good because, otherwise it silently ignores that wrong datetime conversion. I found this issue when my dataset starts to complain because all became duplicated and I was removing the duplicates based on time and I was left with a single row :D , otherwise I wouldn't have caught this issue. Also I tested with this and this also doesn't seem to work even with pyarrow dtypes: df['time'].astype("int64[pyarrow]")

and adding dtype_backed = pyarrow when reading csv seem to work even if the engine = pyarrow.

[jorisvandenbossche]

If pyarrow is not handling properly for that int conversion then should it complain already here ? df['time'] = df['time'].astype('int64') or if the int64 conversion is correct, then when converting back to to_datetime, may be it should complain ?

The problem is that both corrections are correct, if you know that the conversion depends on the specific unit.
In the past, pandas essentially only support nanoseconds, but now it supports seconds up to nanoseconds, and changed the default from nanoseconds to microseconds in pandas 3.0 (https://pandas.pydata.org/docs/whatsnew/v3.0.0.html#datetime-timedelta-resolution-inference).
Because of that change, your code would break in pandas 3.0 regardless of the csv engine you are using.

We have discussed in the past how to make that change more obvious, for example if we should rather just disallow that conversion to integer (i.e. astype("int64") raising an error instead), to avoid the issue you are running into. But in the end there are also valid use cases of wanting to work on the underlying integers, but with the caveat that you now need to be careful about the exact unit you are working with (eg for the conversion back, you could do something like pd.to_datetime(converted_interpolated_ints, unit=df["time"].dtype.unit))

and adding dtype_backed = pyarrow when reading csv seem to work even if the engine = pyarrow.

Not sure why, but when specifying dtype_backed = pyarrow apparently it converts the "s" resolution to "ns" (but only with pandas 2.3, that no longer happens with pandas 3.0, so might be a bug which was fixed in 3.0). So that is the reason your original code still worked as expected when specifying that param.