superset: Error with postgres columns of type "TIMESTAMP WITH TIME ZONE"

  • 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

Hey! I’m using SQL Lab with a postgres backend. I can’t select from some tables, specifically when the column has a type of TIMESTAMP WITH TIME ZONE. An example of the contents of such a cell is 2014-11-24 11:30:02.140381+00

I have other tables with the column of type TIMESTAMP WITHOUT TIME ZONE, and they work fine. Ex: 2010-01-28T16:59:52.900963

Superset version

0.15.1

Expected results

Query should run and return results.

Actual results

I get flashed a 1 second error message saying “Datatype not understood” which then disappears, and then get shown the loading-spinner.

Stack trace:

2017-01-09 17:09:52,437:INFO:root:Running query:
SELECT product_id,
       base_product_id,
       updated
FROM public.product_id_mapping LIMIT 100
2017-01-09 17:09:52,513:ERROR:root:data type not understood
Traceback (most recent call last):
  File "/Users/user/src/superset/venv/lib/python2.7/site-packages/superset/views.py", line 2500, in sql_json
    data = sql_lab.get_sql_results(query_id, return_results=True)
  File "/Users/user/src/superset/venv/lib/python2.7/site-packages/celery/local.py", line 188, in __call__
    return self._get_current_object()(*a, **kw)
  File "/Users/user/src/superset/venv/lib/python2.7/site-packages/celery/app/task.py", line 420, in __call__
    return self.run(*args, **kwargs)
  File "/Users/user/src/superset/venv/lib/python2.7/site-packages/superset/sql_lab.py", line 171, in get_sql_results
    payload['columns'] = cdf.columns_dict if cdf else []
  File "/Users/user/src/superset/venv/lib/python2.7/site-packages/superset/dataframe.py", line 54, in columns_dict
    agg = agg_func(self.__df.dtypes[col], col)
  File "/Users/user/src/superset/venv/lib/python2.7/site-packages/superset/dataframe.py", line 110, in agg_func
    if np.issubdtype(dtype, np.number):
  File "/Users/user/src/superset/venv/lib/python2.7/site-packages/numpy/core/numerictypes.py", line 755, in issubdtype
    return issubclass(dtype(arg1).type, arg2)
TypeError: data type not understood

Steps to reproduce

Run a SELECT query against a postgresql db table that has a column of type TIMEZONE WITH TIME STAMP

About this issue

  • Original URL
  • State: closed
  • Created 7 years ago
  • Reactions: 1
  • Comments: 18 (9 by maintainers)

Commits related to this issue

Most upvoted comments

After poking at this a bit more, it appears the issue is not entirely resolved by updating Pandas. The behavior I’ve observed is as follows:

Prior to updating, an exception would be thrown on any selection from a table containing a timestamp with time zone column, regardless of whether or not that column was selected. After updating, it’s possible to select from such a table, as long as the results themselves do not contain any timestamp with time zone type values. It does appear to be possible to select a timestamp with time zone column as long as it’s you cast to a timestamp without time zone. For example…

SELECT (timevalue_column_name at time zone 'UTC')::timestamp without time zone AS "Timestamp"
FROM table_name;

Applying some function that returns a different type also seems to work fine - converting to date for example:

SELECT date(timevalue_column_name at time zone 'UTC') AS "Date"
FROM table_name;
+5
tfellison on Feb 22, 2017
Read more comments on GitHub
← superset: Error while running superset db upgrade
superset: [Explore] Server Pagination is not working →