Fix wrong save of datetime64[s] in HDFStore

[chaoyihu]

• closes BUG: HDFStore doesn't save datetime64[s] right #59004, BUG: datetime column changed after storing to hdf5 #57085
• [Tests added and passed]
• All code checks passed.
• Added type annotations to new arguments/methods/functions.
• Added an entry in the latest doc/source/whatsnew/v2.2.2.rst file if fixing a bug or adding a new feature.

[chaoyihu]

Thanks for reviewing my code. I've accepted your suggestions and made new commits.

Why did the CI checks fail?

[jbrockmendel]

Might also close #57085?

[chaoyihu]

Might also close #57085?

Will look into that.

[chaoyihu]

The code passed new tests but has regression failure on an old test case in tests/io/pytables/test_round_trip.py::test_table_values_dtypes_roundtrip.

Digging deeper, the current code base seems to be consistently using ns as the time unit, and lots of code are written on top of this assumption. I'm concerned that changing dtype of the _set_tz return value may break this consistency and produce more unwanted effects.

So the alternative I'm thinking is to do a forceful conversion on user input to nanosecond, or issue a warning when user tries to use other dtypes besides datetime64[ns] in a DataFrame. Any thoughts?

[chaoyihu]

Might also close #57085?

I see, thanks @jbrockmendel for linking this issue. If I understand the discussion correctly, non-nano resolutions are not yet fully integrated with the current code base, but changes are intended.

And yes, this fix might also resolve 57085. I think I'll keep working to add support for non-nano time units in HDFStore.

[mroeschke]

Could you use your older whatsnew note instead of this one? Also could you put this in the IO section?

[mroeschke]

Could you use your older test instead of this one? We prefer tests that exercise the public API

[mroeschke]

Any thoughts?

If you're up for it, I think the entire HDF code needs to be updated to support non-nanosecond resolutions i.e. not always coerce things to nanoseconds

[chaoyihu]

I think the code is working now. The CI test xfails on test_to_xarray.py and the reason points to pydata/xarray#9026, which has been fixed in the latest xarray release.

I upgraded to xarray==2024.6 in my local dev environment and the results from pytest pandas/tests/generic/test_to_xarray.py look fine:

================================================= test session starts =================================================
platform linux -- Python 3.10.14, pytest-8.0.2, pluggy-1.4.0
PyQt5 5.15.9 -- Qt runtime 5.15.8 -- Qt compiled 5.15.8
rootdir: /home/pandas
configfile: pyproject.toml
plugins: xvfb-3.0.0, cov-5.0.0, timeout-2.3.1, rerunfailures-14.0, anyio-4.2.0, xdist-3.6.1, cython-0.3.1, localserver-0.0.0, hypothesis-6.103.0, qt-4.4.0
collected 64 items                                                                                                    

pandas/tests/generic/test_to_xarray.py ......................s.........................................

-------------------------------- generated xml file: /home/pandas/test-data.xml --------------------------------
...
============================================ 63 passed, 1 skipped in 0.83s ============================================

[mroeschke]

Thanks @chaoyihu