BUG: to_datetime timestamp parsing in 2.x

[Synse]

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

Passing a unix timestamp as a string with unit='s' parses the wrong timestamp. Passing it as an integer works as expected.

Python 3.10.13 (main, Mar 21 2024, 17:51:02) [GCC 9.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd
>>> pd.__version__
'2.2.1'
>>> pd.to_datetime(1582154775, unit='s')  # correct
Timestamp('2020-02-19 23:26:15')
>>> pd.to_datetime('1582154775', unit='s')  # incorrect, off by 23 seconds
<stdin>:1: FutureWarning: The behavior of 'to_datetime' with 'unit' when parsing strings is deprecated. In a future version, strings will be parsed as datetime strings, matching the behavior without a 'unit'. To retain the old behavior, explicitly cast ints or floats to numeric type before calling to_datetime.
Timestamp('2020-02-19 23:25:52')
>>>

Issue Description

Something changed with pandas.to_datetime() in pandas 2.x and timestamps are now parsing incorrectly if they're passed as a unix timestamp string and units='s'.

There's a deprecation warning about behavior changing in a future version but it appears to be broken in all 2.x releases (reproduced locally on 2.0.0 and 2.2.1).

Expected Behavior

Unix timestamps should continue parse consistently when passed as a str or int until the future version that changes the default behavior.

This does work as expected in the 1.5.3 release:

Python 3.10.13 (main, Mar 21 2024, 17:51:02) [GCC 9.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd
>>> pd.__version__
'1.5.3'
>>> pd.to_datetime(1582154775, unit='s')  # correct
Timestamp('2020-02-19 23:26:15')
>>> pd.to_datetime('1582154775', unit='s')  # correct
Timestamp('2020-02-19 23:26:15')
>>>

Installed Versions

Python 3.10.13 (main, Mar 21 2024, 17:51:02) [GCC 9.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd
>>> pd.show_versions()
/home/codespace/.python/current/lib/python3.10/site-packages/_distutils_hack/__init__.py:33: UserWarning: Setuptools is replacing distutils.
  warnings.warn("Setuptools is replacing distutils.")

INSTALLED VERSIONS
------------------
commit                : bdc79c146c2e32f2cab629be240f01658cfb6cc2
python                : 3.10.13.final.0
python-bits           : 64
OS                    : Linux
OS-release            : 6.2.0-1019-azure
Version               : #19~22.04.1-Ubuntu SMP Wed Jan 10 22:57:03 UTC 2024
machine               : x86_64
processor             : x86_64
byteorder             : little
LC_ALL                : None
LANG                  : C.UTF-8
LOCALE                : en_US.UTF-8

pandas                : 2.2.1
numpy                 : 1.26.4
pytz                  : 2024.1
dateutil              : 2.9.0.post0
setuptools            : 68.2.2
pip                   : 24.0
Cython                : None
pytest                : 8.1.1
hypothesis            : None
sphinx                : None
blosc                 : None
feather               : None
xlsxwriter            : None
lxml.etree            : None
html5lib              : None
pymysql               : None
psycopg2              : None
jinja2                : 3.1.3
IPython               : 8.22.2
pandas_datareader     : None
adbc-driver-postgresql: None
adbc-driver-sqlite    : None
bs4                   : 4.12.3
bottleneck            : None
dataframe-api-compat  : None
fastparquet           : None
fsspec                : 2024.3.1
gcsfs                 : None
matplotlib            : 3.8.3
numba                 : None
numexpr               : None
odfpy                 : None
openpyxl              : None
pandas_gbq            : None
pyarrow               : None
pyreadstat            : None
python-calamine       : None
pyxlsb                : None
s3fs                  : None
scipy                 : 1.12.0
sqlalchemy            : None
tables                : None
tabulate              : None
xarray                : None
xlrd                  : None
zstandard             : None
tzdata                : 2024.1
qtpy                  : None
pyqt5                 : None
>>>

[Synse]

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

3.0.0 looks to be parsing timestamps correctly:

Python 3.10.13 (main, Mar 21 2024, 17:51:02) [GCC 9.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd
>>> pd.__version__
'3.0.0.dev0+666.g6f39c4fa1f'
>>> pd.to_datetime(1582154775, unit='s')  # correct
Timestamp('2020-02-19 23:26:15')
>>> pd.to_datetime('1582154775', unit='s')  # correct
<stdin>:1: FutureWarning: The behavior of 'to_datetime' with 'unit' when parsing strings is deprecated. In a future version, strings will be parsed as datetime strings, matching the behavior without a 'unit'. To retain the old behavior, explicitly cast ints or floats to numeric type before calling to_datetime.
Timestamp('2020-02-19 23:26:15')
>>>