BUG: Inconsistent date time handling with serialized data

[nanthony007]

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

# read a data frame from an excel that contains year formatted column
# example: "DOD" with a format like "1/1/2015"
df1 = pd.read_excel("<link>")

# write to json lines
df.to_json("out.json", orient="records", lines=True)

# read from json lines
df2 = pd.read_json("out.json", lines=True, orient="records", typ="frame")

# at this point, the "DOD" column in df2 has been corrupted

# can be remedied with:
df2["DOD"] = pd.to_datetime(df2["DOD"], unit="ms")

Issue Description

There is inconsistency in pandas I/O regarding serialization to json.

When pandas reads in a file from a source it is sometimes smart enough to infer the date time columns datatype. This is great. However, when that data frame is written to json lines file, it serializes those date times into ISO timestamps. This is not a problem in and of itself. The issue arises when you then try to read that new file. For some reason, the ISO timestamps become corrupted. While the re-read data correctly identifies it as a date time column, all of the data reflects timestamps in 1970. This is extremely confusing as I would've expected pandas' I/O to be compatible when the writer and reader both used the same file format (json) and the default options.

While the above code sample shows a remedy, it is extremely confusing because the column does get parsed as a date time type. If it were, for example, simply parsed as a string, I would know to parse it myself.

This is my first issue so I hope I successfully explained the problem. If this is intended behavior that is fine I was looking for either: A) an explanation of why this occurs, or B) to provide a bug report.

If this part of the code is written in Python I would also be willing to attempt to tackle any changes needed but would need some guidance as a first time contributor.

Thank you for your time and the great work you do! :)

Expected Behavior

I would expect the serialization to match. So I would expect the current behavior where it parses the date time field type. However it parses the incorrect date time and that is the issue.

Installed Versions

Details

INSTALLED VERSIONS

commit : e68db09
python : 3.14.6
python-bits : 64
OS : Darwin
OS-release : 25.5.0
Version : Darwin Kernel Version 25.5.0: Tue Jun 9 22:18:58 PDT 2026; root:xnu-12377.121.10~1/RELEASE_ARM64_T6000
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 3.0.5
numpy : 2.5.1
dateutil : 2.9.0.post0
pip : None
Cython : None
sphinx : None
IPython : 9.16.1
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : None
bottleneck : None
fastparquet : None
fsspec : None
html5lib : None
hypothesis : None
gcsfs : None
jinja2 : None
lxml.etree : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : 3.1.5
psycopg2 : None
pymysql : None
pyarrow : None
pyiceberg : None
pyreadstat : None
pytest : None
python-calamine : None
pytz : None
pyxlsb : 1.0.10
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : 2.0.2
xlsxwriter : 3.2.9
zstandard : None
qtpy : None
pyqt5 : None

[Ankita7033]

I dug into this and the root cause is a unit-inference mismatch between the writer and reader, not corruption of the timestamps themselves.

On write, to_json defaults to date_format="epoch" with date_unit="ms", so datetimes are serialized as epoch milliseconds (e.g. 2015-01-01 → 1420070400000).

On read, read_json doesn't know the unit — date_unit defaults to None, so Parser._try_convert_to_date guesses by trying ("s", "ms", "us", "ns") in order and taking the first that fits within nanosecond bounds. Because this is greedy and order-dependent, the chosen unit depends on the magnitude of the values: large ms timestamps happen to skip "s" (out-of-bounds) and land on "ms", but smaller values can be accepted as "s" first, which is what plants everything near 1970.

So the same file round-trips differently depending on the timestamp values, which is why this is so confusing — passing date_unit="ms" (matching the writer's default) fixes it, as does the reporter's manual to_datetime(..., unit="ms").

A couple of questions before anyone picks this up, since the epoch default is itself deprecated (Pandas4Warning) in favor of ISO:

Is the intended long-term answer here just to steer users to orient="table" / ISO for lossless round-trips, making this a docs/deprecation matter rather than a bug fix?
Or is there appetite for making the epoch read path less surprising in the interim — e.g. preferring the writer's default date_unit on read, or warning when unit inference is ambiguous?

Happy to put up a PR in whichever direction the team prefers.

[Ankita7033]

I tried to reproduce this on main (commit f90323d) and couldn't get corruption with clean inputs — round-trips of both modern dates (2015-01-01 → 1420070400000 → back to 2015-01-01) and near-epoch dates (1970-06-01 → 13046400000 → back to 1970-06-01) came back correct, because read_json's unit inference tries seconds first, overflows the nanosecond bounds, and correctly falls to milliseconds.

Since your data originates from read_excel, the mis-detection is likely specific to the exact values Excel produces. Could you share:

The raw JSON line that to_json writes for the DOD column (just one line, e.g. {"DOD": }), and
The original DOD value it came from?

That number is the key — it'll tell us exactly which unit the reader is mis-inferring and let us reproduce it deterministically without the Excel file.

[nanthony007]

I'll provide another file with different dates since you tried the 2015 ones :)

output.csv

DOD
2018-01-01
2018-01-02
2018-01-03
2018-01-03
2018-01-04

output.jsonl

{"DOD":1514764800000}
{"DOD":1514851200000}
{"DOD":1514937600000}
{"DOD":1514937600000}
{"DOD":1515024000000}

Perhaps this was simply me misunderstanding the deprecation warning as well. I understand the above comments around specifying the date format option which I think are valid feedback. Maybe this is a matter of simply updating documentation. My confusion arose due to the "default" options somewhat conflicting. Happy to see that if we provide the date_format option consistently it does work though.

[nanthony007]

Sorry for the follow up, but I think personally what I would like to see if this functionality is intended based on the options is documentation that the reader and writer do, in fact, have unit mis-match that can result in data loss without matching date_format options. This feels intuitive in hindsight, though but a line in the docs somewhere would be helpful especially given the defaults are in conflict. This is if I'm understanding the work you did above, but it also may be resolved on main.

Thank you.

[Ankita7033]

Confirmed on main: with the values you posted, the DOD column round-trips back as int64 rather than being corrupted to 1970 — read_json's automatic date conversion only fires for columns whose name matches a built-in heuristic (ends with _at/_time, or is date/datetime/timestamp*), and DOD doesn't match, so it's left as raw integers. Columns that do match the name heuristic are where the unit-mismatch can actually produce wrong dates.

Agreed this is really a documentation matter rather than a code bug: to_json defaults to date_format="epoch" with date_unit="ms", while read_json infers the unit independently, so the two defaults can disagree and lose information unless date_format/date_unit are set consistently on both sides. (Worth noting the epoch default is already deprecated in favor of ISO, which would eventually remove the mismatch.)

Would a short note in the read_json/to_json docs — flagging that the epoch round-trip is lossy across mismatched units and recommending orient="table" or matched date_unit — be welcome? Happy to put that up if so.

[nanthony007]

Yes I think the note about epoch being lossy would suffice. Personally I'd recommend just setting the date units and date formats to match since I can't remember off the top of my head the difference between frame and table.

Given this will be resolved with iso format in pandas 4, maybe the documentation only needs to exist in pandas 3 until then.

I would like this note surfaced in the doc strings so it appears in my editor and I don't have to search official pandas site.

Thank you!