error when using time since > 28 days ago and time grain different than time column

[xrmx]

I get this exception:

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

while on a Time series bar chart and using time since > 28 days ago and time grain different than time column. Any clue on what could be wrong?

[xrmx]

Looks like this: pandas-dev/pandas#7139
But should be fixed and tested since a year. Trying to upgrade pandas to 0.18.1 to see if it has been fixed here.

[mistercrunch]

Can you share the full stack trace? Otherwise it's hard to know where the error is coming from...

[xrmx]

So it looks like i have data with mixed timezones, but from a quick look my datetimes are in the very same format:

giu 10 18:37:36 runcaravel[1486]: 2016-06-10 18:37:36,349:ERROR:root:Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True
giu 10 18:37:36  runcaravel[1486]: Traceback (most recent call last):
giu 10 18:37:36 runcaravel[1486]: File "/var/www/caravel/venv/local/lib/python3.4/site-packages/pandas/tseries/tools.py", line 399, in _convert_listlike
giu 10 18:37:36 runcaravel[1486]: values, tz = tslib.datetime_to_datetime64(arg)
giu 10 18:37:36 runcaravel[1486]: File "pandas/tslib.pyx", line 1574, in pandas.tslib.datetime_to_datetime64 (pandas/tslib.c:29550)
giu 10 18:37:36 runcaravel[1486]: ValueError: Array must be all same time zone
giu 10 18:37:36 runcaravel[1486]: During handling of the above exception, another exception occurred:
giu 10 18:37:36 runcaravel[1486]: Traceback (most recent call last):
giu 10 18:37:36 runcaravel[1486]: File "/var/www/caravel/venv/src/caravel/caravel/views.py", line 722, in explore
giu 10 18:37:36 runcaravel[1486]: payload = obj.get_json()
giu 10 18:37:36 runcaravel[1486]: File "/var/www/caravel/venv/src/caravel/caravel/viz.py", line 250, in get_json
giu 10 18:37:36 runcaravel[1486]: 'data': self.get_data(),
giu 10 18:37:36 runcaravel[1486]: File "/var/www/caravel/venv/src/caravel/caravel/viz.py", line 1014, in get_data
giu 10 18:37:36 runcaravel[1486]: df = self.get_df()
giu 10 18:37:36 runcaravel[1486]: File "/var/www/caravel/venv/src/caravel/caravel/viz.py", line 923, in get_df
giu 10 18:37:36 runcaravel[1486]: df = super(NVD3TimeSeriesViz, self).get_df(query_obj)
giu 10 18:37:36 runcaravel[1486]: File "/var/www/caravel/venv/src/caravel/caravel/viz.py", line 143, in get_df
giu 10 18:37:36 runcaravel[1486]: df.timestamp = pd.to_datetime(df.timestamp, utc=False)
giu 10 18:37:36 runcaravel[1486]: File "/var/www/caravel/venv/local/lib/python3.4/site-packages/pandas/util/decorators.py", line 91, in wrapper
giu 10 18:37:36 runcaravel[1486]: return func(*args, **kwargs)
giu 10 18:37:36 runcaravel[1486]: File "/var/www/caravel/venv/local/lib/python3.4/site-packages/pandas/tseries/tools.py", line 287, in to_datetime
giu 10 18:37:36 runcaravel[1486]: unit=unit, infer_datetime_format=infer_datetime_format)
giu 10 18:37:36 runcaravel[1486]: File "/var/www/caravel/venv/local/lib/python3.4/site-packages/pandas/tseries/tools.py", line 409, in _to_datetime
giu 10 18:37:36 runcaravel[1486]: values = _convert_listlike(arg._values, False, format)
giu 10 18:37:36 runcaravel[1486]: File "/var/www/caravel/venv/local/lib/python3.4/site-packages/pandas/tseries/tools.py", line 402, in _convert_listlike
giu 10 18:37:36  runcaravel[1486]: raise e
giu 10 18:37:36 runcaravel[1486]: File "/var/www/caravel/venv/local/lib/python3.4/site-packages/pandas/tseries/tools.py", line 388, in _convert_listlike
giu 10 18:37:36 runcaravel[1486]: require_iso8601=require_iso8601
giu 10 18:37:36 runcaravel[1486]: File "pandas/tslib.pyx", line 1960, in pandas.tslib.array_to_datetime (pandas/tslib.c:39429)
giu 10 18:37:36 runcaravel[1486]: File "pandas/tslib.pyx", line 2000, in pandas.tslib.array_to_datetime (pandas/tslib.c:36393)
giu 10 18:37:36  runcaravel[1486]: ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True

[xrmx]

Same happens on pandas 0.18.1

UPDATE: hacked pandas git master to show a bit more info and i get this:

File "pandas/tslib.pyx", line 1656, in pandas.tslib.datetime_to_datetime64 (pandas/tslib.c:30124)
ValueError: Array must be all same time zone: psycopg2.tz.FixedOffsetTimezone(offset=60, name=None) vs inferred psycopg2.tz.FixedOffsetTimezone(offset=120, name=None)

Given the time span and the hour difference it could be the Daylight Saving Time change.

[xrmx]

I've tried to poke a bit at this but i can't see a generic solution here:

• pandas gets psycopg2.tz.FixedOffsetTimezone tz objects so their dst(dt) method will always return 0 and thus we cannot adjust the check here to keep dst into account
• caravel handle the objects returned by the query as a whole

Any other ideas or a clue?

[xrmx]

I overlooked the fact that the field i use as time column is a DATE so it has not time or timezone. So it's something inside caravel / pandas that is upgrading this to a Timestamp. Any hint on where to look?

UPDATE: it looks like using a timestamp field avoid the issue. Fixes my immediate issue (yay!) but can we do something to avoid the pain to the next person?

[kurtbradd]

I'm also receiving this exception on an attribute stored as a timestamp with time zone.
I've tried upgrading to Pandas 0.18.1 with no luck.

[xrmx]

@kurtbradd do you get "ValueError: Array must be all same time zone"? If so are you using postgresql? If not that's probably a different issue :)

[danialgood]

@xrmx i get the error you mentioned and i'm using postgresql

[mistercrunch]

runcaravel[1486]: File "/var/www/caravel/venv/src/caravel/caravel/viz.py", line 143, in get_df
giu 10 18:37:36 runcaravel[1486]: df.timestamp = pd.to_datetime(df.timestamp, utc=False)

I'm not sure if utc=False is really needed here or whether is should be configurable at the column level. Anyhow, a quick fix might be to wrap this in a try statement and fallback on utc=True when the utc=False call fails.

[danialgood]

my timestamps have timezone different than utc
i ended up casting column ({col}) to TIMESTAMP
no problems so far