[Python] Specify behavior for converting tz-aware datetime.datetime objects to Arrow format

[asfimport]

The original description is at pandas-dev/pandas#32587

Code Sample, a copy-pastable example if possible

import pandas as pd
from datetime import datetime, timezone

df = pd.DataFrame.from_records([
    (1, datetime.now().replace(tzinfo=timezone.utc)),
    (2, datetime.now().replace(tzinfo=timezone.min))],
    columns=["1", "2"])

print(df["2"])
print()

df.to_feather("/tmp/1") 
df2 = pd.read_feather("/tmp/1")

print(df2["2"])

This code will output:

0    2020-03-10 18:13:49.405598+00:00
1    2020-03-10 18:13:49.405626-23:59
Name: 2, dtype: object

0   2020-03-10 18:13:49.405598
1   2020-03-10 18:13:49.405626
Name: 2, dtype: datetime64[ns]

Problem description

The round-trip dtype changed from the correct object to incorrect datetime64. Thus the timezones were discarded in Arrow and the timestamps became invalid.

Expected Output

(identical)

0    2020-03-10 18:13:49.405598+00:00
1    2020-03-10 18:13:49.405626-23:59
Name: 2, dtype: object

0    2020-03-10 18:13:49.405598+00:00
1    2020-03-10 18:13:49.405626-23:59
Name: 2, dtype: object

Output of pd.show_versions()

INSTALLED VERSIONS
------------------
commit           : None
python           : 3.7.5.final.0
python-bits      : 64
OS               : Linux
OS-release       : 5.3.0-40-generic
machine          : x86_64
processor        : x86_64
byteorder        : little
LC_ALL           : None
LANG             : en_US.UTF-8
LOCALE           : en_US.UTF-8

pandas           : 1.0.1
numpy            : 1.17.4
pytz             : 2019.2
dateutil         : 2.7.3
pip              : 19.3.1
setuptools       : 42.0.1
Cython           : 0.29.14
pytest           : 5.3.1
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : 4.5.0
html5lib         : None
pymysql          : None
psycopg2         : 2.8.4 (dt dec pq3 ext lo64)
jinja2           : 2.10.3
IPython          : 7.10.0
pandas_datareader: None
bs4              : 4.8.1
bottleneck       : None
fastparquet      : None
gcsfs            : None
lxml.etree       : 4.5.0
matplotlib       : 3.1.2
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : 0.16.0
pytables         : None
pytest           : 5.3.1
pyxlsb           : None
s3fs             : None
scipy            : 1.2.1
sqlalchemy       : 1.3.12
tables           : None
tabulate         : None
xarray           : None
xlrd             : None
xlwt             : None
xlsxwriter       : None
numba            : None

Reporter: Markovtsev Vadim

Related issues:

• [Python] Honor tzinfo information when converting from datetime to pyarrow (is duplicated by)

_{Note: This issue was originally created as ARROW-8066. Please see the migration documentation for further details.}

[asfimport]

Wes McKinney / @wesm:
Can you copy the details to this issue?

[asfimport]

Markovtsev Vadim:
@wesm Done. +100 to my pain (y)

[asfimport]

Wes McKinney / @wesm:
Thanks.

I believe we don't have any handling of tz-aware datetime.datetime objects when converting to the Arrow format. Best case scenario all values have the same time zone, but we will need to decide what happens when they have different time zones. We won't be able to have each value have its own different time zone at the moment, though

Options for what should take place:

1. UTC-normalize tz-aware datetime.datetime
2. When all values have the same time zone, convert the tzinfo to a timezone string for storage in the Arrow metadata
3. When there are distinct tzinfos, we should do one of two things: either UTC-normalize (and set timezone utc) or raise an exception

Either way there's a fair bit of work to do to accomplish these

[asfimport]

Joris Van den Bossche / @jorisvandenbossche:
At least we should normalize to UTC, I think (now it is just taking the "local" time with discarding the time zone, it seems).

BTW,I don't think that such roundtrip can ever work (automatically). In addition to not being able to support different timezones within an array as Wes mentioned, converting TimestampArray back to pandas will also prefer datetime64 (and I don't think we have an option to specify you want datetime.datetime objects?)

[asfimport]

Markovtsev Vadim:
Yep, converting mixed timezones to UTC would make much sense IMO.

Pandas will prefer datetime64 if the items are within the allowed range (year 1677 - year 2262) and the timezone is the same, otherwise, it will resort to datetime.datetime.