Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True

[psinger]

Make sure these boxes are checked before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if any
• I have reproduced the issue with at least the latest released version of superset
• I have checked the issue tracker for the same issue and I haven't found one similar

Superset version

current master

Expected results

chart

Actual results

error: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True

Steps to reproduce

Use date column in postgresql

I am receiving the error Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True when I try to use a timestamp column that is a date column in postgresql. Timestamp columns work fine. Python/pandas trace:

2018-01-19 16:34:23,678:ERROR:root:Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True
Traceback (most recent call last):
File "/home/x/anaconda3/lib/python3.6/site-packages/pandas/core/tools/datetimes.py", line 444, in _convert_listlike
values, tz = tslib.datetime_to_datetime64(arg)
File "pandas/_libs/tslib.pyx", line 1796, in pandas._libs.tslib.datetime_to_datetime64 (pandas/_libs/tslib.c:33086)
ValueError: Array must be all same time zone

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/home/x/tmp/incubator-superset/superset/viz.py", line 273, in get_payload
df = self.get_df(query_obj)
File "/home/x/tmp/incubator-superset/superset/viz.py", line 124, in get_df
df[DTTM_ALIAS], utc=False, format=timestamp_format)
File "/home/x/anaconda3/lib/python3.6/site-packages/pandas/core/tools/datetimes.py", line 509, in to_datetime
values = _convert_listlike(arg._values, False, format)
File "/home/x/anaconda3/lib/python3.6/site-packages/pandas/core/tools/datetimes.py", line 447, in _convert_listlike
raise e
File "/home/x/anaconda3/lib/python3.6/site-packages/pandas/core/tools/datetimes.py", line 435, in _convert_listlike
require_iso8601=require_iso8601
File "pandas/_libs/tslib.pyx", line 2355, in pandas._libs.tslib.array_to_datetime (pandas/_libs/tslib.c:46617)
File "pandas/_libs/tslib.pyx", line 2401, in pandas._libs.tslib.array_to_datetime (pandas/_libs/tslib.c:43251)
ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True

[xrmx]

which database is this?

[psinger]

@xrmx Postgresql

[psinger]

If one changes pd.to_datetime(df[DTTM_ALIAS], utc=False, format=timestamp_format) in viz.py to pd.to_datetime(df[DTTM_ALIAS], utc=True, format=timestamp_format), it works. But that does not seem to be the best solution as datetime might be tz aware.

[habalux]

Hi,

This seems to happen because of postgresql DATE_TRUNC() function which returns a "timestamp with time zone" when given a "date" column and therefore causing the error with pandas. I fixed it by casting the function call back to "timestamp without time zone" in db_engine_specs.py - probably not the best solution either but at least it was enough to circumvent the problem for the time being.

[mistercrunch]

@habalux mind opening a PR with your change for reference? Others may want to apply the same patch.

[habalux]

@mistercrunch PR submitted, though as I noted it may cause some times to be shown incorrectly since the tz information is stripped.

[MartinThoma]

(People coming here via Google might be interested in this stackoverflow question)

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[satvikathakur]

utc=True, format=timestamp_format

thanks this works

[davidoevans]

utc=True, format=timestamp_format

thanks this works

@satvikathakur - were you able to specify that setting within superset when defining the table or did you have to change source code?