Fix wrong save of datetime64[s] in HDFStore
- [x] closes #59004, #57085
- [x] [Tests added and passed]
- [x] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [x] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [x] Added an entry in the latest `doc/source/whatsnew/v2.2.2.rst` file if fixing a bug or adding a new feature.

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

Why did the CI checks fail? Might also close #57085? > Might also close #57085?

Will look into that. 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`](https://github.com/pandas-dev/pandas/actions/runs/9524912263/job/26258401584?pr=59018#step:9:282).

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? > 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. > 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 I think the code is working now. The CI test xfails on `test_to_xarray.py` and the reason points to https://github.com/pydata/xarray/issues/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 ============================================
```
 Thanks @chaoyihu 