Datetime field formatting breaks for datetimes before 1678-1-1 when reading from file

[apon97]

Hello,

there seems to be some issue with how datetime columns are read when reading a dataframe from file, where, if the file contains a date < 1678-01-01 (i guess something to do with numpy/pandas using ns resolution for datetime), then the values in the column are formatted differently, and are not interpreted as a datetime column.

from io import StringIO

# import geopandas as gpd


# for year in range(1670, 1680, 1):
#     age = f"{year}-01-01T00:00:00"
#     geojson_string = f"""
#     {{
#         "type": "FeatureCollection",
#         "features": [
#             {{
#                 "type": "Feature",
#                 "properties": {{
#                     "age": "{age}"
#                 }},
#                 "geometry": null
#             }}
#         ]
#     }}
#     """
#     geojson_io = StringIO(geojson_string)
#     gdf = gpd.read_file(geojson_io, engine="pyogrio")
#     print(gdf.age.iloc[0])

# print()

# test the same with pyogrio directly
import pyogrio

for year in range(1670, 1680, 1):
    age = f"{year}-01-01T00:00:00"
    geojson_string = f"""
    {{
        "type": "FeatureCollection",
        "features": [
            {{
                "type": "Feature",
                "properties": {{
                    "age": "{age}"
                }},
                "geometry": null
            }}
        ]
    }}
    """
    geojson_io = StringIO(geojson_string)
    gdf = pyogrio.read_dataframe(geojson_io)
    print(gdf["age"].iloc[0])

output:

1670/01/01 00:00:00
1671/01/01 00:00:00
1672/01/01 00:00:00
1673/01/01 00:00:00
1674/01/01 00:00:00
1675/01/01 00:00:00
1676/01/01 00:00:00
1677/01/01 00:00:00
1678-01-01 00:00:00
1679-01-01 00:00:00

In my case, i have some automated pipelines that write the datetime in iso format string, then read them back to dataframe. But as soon as there is a datetime older than 1678 (and bigger than 2262 i guess), the formatting breaks.

The formatting is consistent when using geopandas with fiona.

As a side note, I would be fine with the column being read as a string column, but that does not seem possible with geopandas. Is that the case, or am I missing something?

Thank you

versions: geopandas 0.14.4, pyogrio 0.11.0

[jorisvandenbossche]

This is indeed caused by the nanosecond resolution limitation of pandas. More recent versions of pandas actually support lower resolution, but not yet at the moment of parsing in to_datetime .. (with the upcoming pandas 3.0 version, this will actually get fixed).

As a side note, I would be fine with the column being read as a string column, but that does not seem possible with geopandas. Is that the case, or am I missing something?

The column is actually read as string for those earlier dates?

[apon97]

This is indeed caused by the nanosecond resolution limitation of pandas. More recent versions of pandas actually support lower resolution, but not yet at the moment of parsing in to_datetime .. (with the upcoming pandas 3.0 version, this will actually get fixed).

Funny though that the type of the datetime read by pyogrio is actually lower resolution datetime64[ms]. I guess it is converted afterwards?

from io import StringIO
import geopandas as gpd


def print_age(year: int, engine: str):
    age = f"{year}-01-01T00:00:00"
    geojson_string = f"""
    {{
        "type": "FeatureCollection",
        "features": [
            {{
                "type": "Feature",
                "properties": {{
                    "age": "{age}"
                }},
                "geometry": null
            }}
        ]
    }}
    """
    geojson_io = StringIO(geojson_string)
    gdf = gpd.read_file(geojson_io, engine=engine)
    print(gdf["age"])


print_age(1677, "pyogrio")
print_age(1677, "fiona")

print()

print_age(1678, "pyogrio")
print_age(1678, "fiona")

results in

0    1677/01/01 00:00:00
Name: age, dtype: object
0    1677-01-01T00:00:00
Name: age, dtype: object

0   1678-01-01
Name: age, dtype: datetime64[ms]
0   1678-01-01
Name: age, dtype: datetime64[ns]

The column is actually read as string for those earlier dates?

So for datetimes < 1678, both fiona and pyogrio read the column as object, but pyogrio changes (and breaks in my case) formatting, while fiona keeps the formatting from the file. Is that expected? Maybe just something to do with my locale?

[jorisvandenbossche]

Funny though that the type of the datetime read by pyogrio is actually lower resolution datetime64[ms]. I guess it is converted afterwards?

Indeed, because GDAL only supports that resolution, we convert it to ms after parsing the strings.

So for datetimes < 1678, both fiona and pyogrio read the column as object, but pyogrio changes (and breaks in my case) formatting, while fiona keeps the formatting from the file. Is that expected?

Fiona does not actually keep the formatting of the file, it just happens to be the same. What fiona does in practice is get the datetime value from GDAL (OGR_F_GetFieldAsDateTimeEx, which gets all year, day, month, hour, etc componnents), create a datetime.datetime object from that, and then format it with .isoformat() (and because you also used ISO formatting in the file, it looks the same).

In the case of pyogrio, it can do the same, but under the hood it has a datetime_as_string parameter to fetch the value from GDAL as a string (which is faster than going through a datetime.datetime object) But, GDAL has this specific gotcha that it formats the string with / instead of - ...
When reading into a dataframe, pyogrio sets this to True to then use pandas to parse the strings. But if that parsing fails, then it returns those strings to the user, and you see the original GDAL formatting (and at the moment, there is no way to override this behaviour using geopandas)

Now, GDAL has added another method to get an ISO formatted string (on our request), so we should at some point start using that:

pyogrio/pyogrio/_io.pyx

Lines 937 to 941 in f176565

	if datetime_as_string:
	# defer datetime parsing to user/ pandas layer
	# Update to OGR_F_GetFieldAsISO8601DateTime when GDAL 3.7+ only
	data[i] = get_string(
	OGR_F_GetFieldAsString(ogr_feature, field_index), encoding=encoding

[theroggy]

FYI... there is a PR on the way that makes many improvements to how pyogrio deals with datetime columns, including offering an option to return datetime columns as strings: #486