BUG: converting to datetime with unit=D, unit=M, or unit=Y fails silently (regression)

[pfiddy]

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
from datetime import datetime

col = pd.Series([datetime.now() for _ in range(5)])

col_day = col.astype('datetime64[D]')
# the following line should return True but it now returns False
all([t.hour == 0 and t.minute == 0 and t.second == 0 and t.microsecond == 0 for t in col_day])


col_day = pd.to_datetime(col, unit='D')
# the following line should return True but it now returns False
all([t.hour == 0 and t.minute == 0 and t.second == 0 and t.microsecond == 0 for t in col_day])

col_month = col.astype('datetime64[M]')
# the following line should return True but it now returns False
all([t.day == 1 and
     t.hour == 0 and t.minute == 0 and t.second == 0 and t.microsecond == 0
     for t in col_month])

col_month = pd.to_datetime(col, unit='M')
# the following line should return True but it now returns False
all([t.day == 1 and
     t.hour == 0 and t.minute == 0 and t.second == 0 and t.microsecond == 0
     for t in col_month])

col_year = col.astype('datetime64[Y]')
# the following line should return True but it now returns False
all([t.month == 1 and t.day == 1 and
     t.hour == 0 and t.minute == 0 and t.second == 0 and t.microsecond == 0
     for t in col_year])

col_year = pd.to_datetime(col, unit='Y')
# the following line should return True but it now returns False
all([t.month == 1 and t.day == 1 and
     t.hour == 0 and t.minute == 0 and t.second == 0 and t.microsecond == 0
     for t in col_year])

Issue Description

Converting a datetime to a datetime with unit='D', 'M', or 'Y' has stopped working, both with .astype() and to_datetime().

This issue first appeared in version 1.5.0 (confirmed that it's not present in 1.4.4) and is still present in the latest version (1.5.3).

Expected Behavior

See example code above -- the lines below the comments should evaluate to True instead of False. (In other words: times should disappear from the data when unit='D', 'M', or 'Y'; days should disappear from the data when unit='M' or 'Y'; and only the year should remain in the data when unit='Y'.)

Installed Versions

INSTALLED VERSIONS

commit : 2e218d1
python : 3.10.8.final.0
python-bits : 64
OS : Darwin
OS-release : 21.6.0
Version : Darwin Kernel Version 21.6.0: Sun Nov 6 23:31:13 PST 2022; root:xnu-8020.240.14~1/RELEASE_ARM64_T6000
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : None
LOCALE : en_US.UTF-8
pandas : 1.5.3
numpy : 1.23.4
pytz : 2022.6
dateutil : 2.8.2
setuptools : 65.5.1
pip : 22.0.4
Cython : 0.29.26
pytest : 6.2.5
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.9.1
html5lib : None
pymysql : 1.0.2
psycopg2 : 2.9.5
jinja2 : 3.1.2
IPython : None
pandas_datareader: None
bs4 : 4.11.1
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : 0.17.8
pyarrow : 8.0.0
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.9.3
snappy : None
sqlalchemy : 1.4.41
tables : None
tabulate : 0.9.0
xarray : None
xlrd : None
xlwt : None
zstandard : None
tzdata : 2022.6

[pfiddy]

I suspect this may have been introduced by #47579 based on the code path I see during debugging, but I haven't confirmed this.

If there's a new preferred way to reduce the precision of datetime64s (to shave off time, day, and month data) please let me know. The current docs for to_datetime suggest that 'D' should still be a valid unit, at least (see the section about the unit parameter).

[pfiddy]

Ideally, all the date units supported by numpy ('Y', 'M', 'W', and 'D') would work in pandas as they used to (see https://numpy.org/doc/stable/reference/arrays.datetime.html#basic-datetimes )

[MarcoGorelli]

thanks @pfiddy for the report!

'D' is a valid unit in to_datetime, but not a valid resolution

I think this just needs documenting better

[pfiddy]

Hi @MarcoGorelli, thanks for the response! Can you clarify what you mean by "resolution" there? Are you talking about a resolution to this issue, or a resolution in the sense of a numerical precision on the datetime type, or something else?

[MarcoGorelli]

Hi @pfiddy

Yeah it's the precision of the dtype, which can be: datetime64[s], datetime64[ms], datetime64[us], datetime64[ns] (this is still quite bleeding-edge, and new in 2.0, though some preparatory work went into 1.5)

'datetime64[D]' will raise in pandas 2.0

for your case, if you just want the date part, would this work?

In [16]: col
Out[16]: 
0   2023-01-31 20:33:12.191560
1   2023-01-31 20:33:12.191563
2   2023-01-31 20:33:12.191563
3   2023-01-31 20:33:12.191563
4   2023-01-31 20:33:12.191563
dtype: datetime64[ns]

In [17]: col.dt.normalize()
Out[17]: 
0   2023-01-31
1   2023-01-31
2   2023-01-31
3   2023-01-31
4   2023-01-31
dtype: datetime64[ns]

?

[willsthompson]

I'm from the same organization, and I just wanted to report that this will work. At the moment we are using pd.Series(col.to_numpy().astype(f"datetime64[D]")) because in our function "D" is a parameter, but we may change that to the Pandas-native method you suggested in the future. Thanks for your help.