BUG: to_datetime drops decimal point and fractional seconds under certain conditions

[DaveSprague]

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.

When a time string has a whole number of seconds (the fractional part is zero) followed by a timezone specification such as "+00:00", for example,

"2021-09-17 22:07:15.000000+00:00",

the to_datetime function drops the decimal point and the fraction zeros which results in a DateTime object like this:

"2021-09-17 22:07:15+00:00",

If this DateTime object is then exported to a csv file and read back in, a subsequent to_datetime_ function will crash with an ValueError like this one:

ValueError: time data "2021-09-17 22:07:15+00:00" doesn't match format "%Y-%m-%d %H:%M:%S.%f%z"

Reproducible Example

Code:

import pandas as pd

df = pd.DataFrame({"value": range(3), "DateTime": [
    '2021-09-17 22:07:14.820200+00:00',
    '2021-09-17 22:07:15.000000+00:00',
    '2021-09-17 22:07:15.179962+00:00',
]})

print(df)

df["convertedDateTime"] = pd.to_datetime(df.DateTime)

print(df)

df.to_csv("test_to_datetime.csv")

df_in = pd.read_csv("test_to_datetime.csv")

df_in["twice_converted_datetime"] = pd.to_datetime(df_in["convertedDateTime"])

print(df_in)


Output:
'''
   value                          DateTime
0      0  2021-09-17 22:07:14.820200+00:00
1      1  2021-09-17 22:07:15.000000+00:00
2      2  2021-09-17 22:07:15.179962+00:00
   value                          DateTime                convertedDateTime
0      0  2021-09-17 22:07:14.820200+00:00 2021-09-17 22:07:14.820200+00:00
1      1  2021-09-17 22:07:15.000000+00:00        2021-09-17 22:07:15+00:00
2      2  2021-09-17 22:07:15.179962+00:00 2021-09-17 22:07:15.179962+00:00
Traceback (most recent call last):
  File "test_to_datetime.py", line 28, in <module>
    df_in["twice_converted_datetime"] = pd.to_datetime(df_in["convertedDateTime"])
  File "C:\Users\David\anaconda3\lib\site-packages\pandas\core\tools\datetimes.py", line 1112, in to_datetime
    values = convert_listlike(arg._values, format)
  File "C:\Users\David\anaconda3\lib\site-packages\pandas\core\tools\datetimes.py", line 488, in _convert_listlike_datetimes
    return _array_strptime_with_fallback(arg, name, utc, format, exact, errors)
  File "C:\Users\David\anaconda3\lib\site-packages\pandas\core\tools\datetimes.py", line 519, in _array_strptime_with_fallback
    result, timezones = array_strptime(arg, fmt, exact=exact, errors=errors, utc=utc)
  File "strptime.pyx", line 534, in pandas._libs.tslibs.strptime.array_strptime
  File "strptime.pyx", line 355, in pandas._libs.tslibs.strptime.array_strptime
ValueError: time data "2021-09-17 22:07:15+00:00" doesn't match format "%Y-%m-%d %H:%M:%S.%f%z", at position 1. You might want to try: 
    - passing `format` if your strings have a consistent format;
    - passing `format='ISO8601'` if your strings are all ISO8601 but not necessarily in exactly the same format;
    - passing `format='mixed'`, and the format will be inferred for each element individually. You might want to use `dayfirst` alongside this.
'''

Issue Description

``When a column that represents datetime as a string has an entry like this one:
'2021-09-17 22:07:15.000000+00:00'
where the fractional seconds are all zeros,
the pandas to_datetime function will convert it to:
2021-09-17 22:07:15+00:00

which drops the decimal point and fractional part of the number of seconds in the datetime representation.

If you write that converted dataframe to a csv file and read it back in again,
the to_datetime function will fail because

"2021-09-17 22:07:15+00:00" doesn't match format "%Y-%m-%d %H:%M:%S.%f%z"

Expected Behavior

if you remove the timezone coding at the end of the original strings (i.e. remove the "+00:00", then the above example code works correctly with the following output:

value DateTime
0 0 2021-09-17 22:07:14.820200
1 1 2021-09-17 22:07:15.000000
2 2 2021-09-17 22:07:15.179962
value DateTime convertedDateTime
0 0 2021-09-17 22:07:14.820200 2021-09-17 22:07:14.820200
1 1 2021-09-17 22:07:15.000000 2021-09-17 22:07:15.000000
2 2 2021-09-17 22:07:15.179962 2021-09-17 22:07:15.179962
Unnamed: 0 value DateTime convertedDateTime twice_converted_datetime
0 0 0 2021-09-17 22:07:14.820200 2021-09-17 22:07:14.820200 2021-09-17 22:07:14.820200
1 1 1 2021-09-17 22:07:15.000000 2021-09-17 22:07:15.000000 2021-09-17 22:07:15.000000
2 2 2 2021-09-17 22:07:15.179962 2021-09-17 22:07:15.179962 2021-09-17 22:07:15.179962

But the to_datetime function should work properly even when the original strings contain the "+00:00" suffix

Installed Versions

INSTALLED VERSIONS

commit : e86ed37
python : 3.10.12.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.22621
machine : AMD64
processor : Intel64 Family 6 Model 167 Stepping 1, GenuineIntel
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : English_United States.1252

pandas : 2.1.1
numpy : 1.26.0
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 68.2.2
pip : 23.2.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : 8.15.0
pandas_datareader : None
bs4 : None
bottleneck : None
dataframe-api-compat: None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

[Anandesh-Sharma]

@DaveSprague
Actually, you need to set date_format like this and it will work while writing dataframe to csv.

df.to_csv("test_to_datetime.csv", date_format='%Y-%m-%d %H:%M:%S.%f%z')

[paulreece]

@DaveSprague, @Anandesh-Sharma is correct. If you specify your string format for the date then the code works fine.

[DaveSprague]

Thanks for the workaround. I see that your filing a fix to change the default datetime string format for to_csv but isn't the issue larger than this?

Isn't it a bug in the to_datetime function or in the datetime object code that it drops the decimal point in certain cases, when converting the datetime object's value to a string, resulting in datetimes that are formatted inconsistently (some with a decimal point and others without a decimal point)?

The other option would be to change the code in the datetime object so that
a datetime string like "2021-09-17 22:07:15+00:00" does match format the format "%Y-%m-%d %H:%M:%S.%f%z"

[paulreece]

Isn't it a bug in the to_datetime function or in the datetime object code that it drops the decimal point in certain cases, when converting the datetime object's value to a string, resulting in datetimes that are formatted inconsistently (some with a decimal point and others without a decimal point)?

I don't see the issue here. It's just to_datetime is just dropping the zeros for an integer, I'm not sure that it makes sense to implement functionality within that to retain datetime precision for decimals when it's zero. Especially when you use the argument in the method to format it correctly: date_format='%Y-%m-%d %H:%M:%S.%f%z' . There is no issue converting the value to a string when using to_csv when you do.

>>> import pandas as pd
>>> df = pd.DataFrame({"value": range(3), "DateTime": [
...     '2021-09-17 22:07:14.820200+00:00',
...     '2021-09-17 22:07:15.000000+00:00',
...     '2021-09-17 22:07:15.179962+00:00',
... ]})
>>> df["convertedDateTime"] = pd.to_datetime(df.DateTime)
>>> df.to_csv("test_to_datetime.csv", date_format='%Y-%m-%d %H:%M:%S.%f%z')
>>> df_in = pd.read_csv("test_to_datetime.csv")
>>> df_in
   Unnamed: 0  value                          DateTime                convertedDateTime
0           0      0  2021-09-17 22:07:14.820200+00:00  2021-09-17 22:07:14.820200+0000
1           1      1  2021-09-17 22:07:15.000000+00:00  2021-09-17 22:07:15.000000+0000
2           2      2  2021-09-17 22:07:15.179962+00:00  2021-09-17 22:07:15.179962+0000

Note: I am not referring to the opened PR. That doesn't address the issue, which as I elaborated on above there are no code changes needed to get your desired output.

[HananxR]

My pandas'version is 1.5.3,I have also encountered similar problems. I have ruled out using Parallel as it is not related to the system clock, as the inputs to the relevant functions are all deterministic. I tried the method in the 'datatime' package, but similar issues still occur. Finally, I used 'from datautil import parser' to solve the problem. Note: Our original timestamps are all strings, in the form of 07:13:49.351.

from dateutil import parser


class Test:
    @staticmethod
    def adjust_time_stamp(t_time):
        """adjust t_time"""
        return parser.parse(t_time)

[HananxR]

My conclusion after trying:

Effective solutions:

• Use the timestamp automatic parsing function in 'deteutil';
• Use 'pd. to_timedelta';

Invalid plan:

• pd. to_datetime;
• pd. Timestamp;
• datatime. striptime ;

[afargnoli-ops]

I just came across this bug today. The only workaround I have (since the CSV files take hours to generate and have already been generated) is to add "format='mixed'" to my call to this buggy version of to_datetime. I hope this gets fixed in a future version of Pandas though.