I downgraded the version of psycopg2 and psycopg2-binary to 2.8.6 and the problem was fixed. I assume there is a problem with version 2.9.X.

Work fine after modify postgres db engine, and timezone offset problem in explore solved. Steps with docker-compose instance(version=1.3.1).

docker exec -it superset_app /bin/bash
apt-get update
apt-get install vim
vim /app/superset/db_engine_specs/postgres.py 
# delete line 171 (cursor.tzinfo_factory = FixedOffsetTimezone)
exit
# back to host machine
docker-compose -f docker-compose-non-dev.yml restart

psycopg 2.9 release note psycopg2 updated to use datetime.timezone as tz property of datetime object, we do not need pytz._FixedOffset any more.

update 2022-01-11

Should have been resolved by this https://github.com/apache/superset/pull/17713

After further experimentation, the issue seems to be timezones. If I removed the timezone offset with to_char(), my query works.

Update:

I was able to work around the issue with the following query:

SELECT (time at time zone 'CEST')::timestamp without time zone AS "time",
       ma_spo2,
       ma_bpm,
       ma_perf
FROM pulse_ox_moving_average

This hack would allow me to create time-series charts where as using to_char would not. Setting a timezone and then localizing by removing the timezone is not ideal for the long term.

Dowgrading from 2.9.1 -> 2.8.6 was also the solution in my case.

Could quite possibly be related to this change listed in the 2.9 release notes?

Use datetime.timezone objects by default in datetime objects instead of FixedOffsetTimezone

@santicomp2014,

You should just be able to:

• Clone the repo: $ git clone https://github.com/apache/superset.git
• $ cd superset/requirements/
• Change both docker.txt and development.txt to psycopg2-binary==2.8.6
• Run: $ docker-compose -f docker-compose-non-dev.yml up

Following on your suggestion, I fixed it by editing the date column expression in the dataset editor window:

After further experimentation, the issue seems to be timezones. If I removed the timezone offset with to_char(), my query works.

Update:

I was able to work around the issue with the following query:

SELECT (time at time zone 'CEST')::timestamp without time zone AS "time",
       ma_spo2,
       ma_bpm,
       ma_perf
FROM pulse_ox_moving_average

This hack would allow me to create time-series charts where as using to_char would not. Setting a timezone and then localizing by removing the timezone is not ideal for the long term.

This has been fixed on master branch here: #17713 , closing

Glad to see there are workarounds possible, however I’d prefer to use the official Docker image posted to Dockerhub. Any idea when this fix can be made available there?

I can replicate this issue using a fresh install of the latest docker image per the instructions here. Screenshot below. According to a pip freeze in the app container (superset_app), this version is using psycopg2 v. 2.9.1. Manually replacing version 2.9.1 with 2.8.6 as suggested above resolves the issue. @mail2lawi’s solution above (time at time zone ‘CEST’)… also was an effective fix, leading me to support the hypothesis that this issue is specifically related to columns which contain timestamptz values. Thank you for your help all!

Hi @jonathanStrange0 , I’m new to superset. How do you change the version, I cloned master and found ["psycopg2-binary==2.8.5" as a dependency. This version appears to be older than 2.8.6 and I get the same error.

What are the steps to change the version with docker-compose. Thank you very much.

Hi !

For docker-compose-non-dev, here is my “hack”:

    docker-compose -f docker-compose-non-dev.yml up
    docker-compose -f docker-compose-non-dev.yml exec superset pip uninstall -y psycopg2-binary
    docker-compose -f docker-compose-non-dev.yml exec superset pip install psycopg2-binary==2.8.6
    docker-compose -f docker-compose-non-dev.yml restart superset

Before the hack

After the hack

Is this an issue that will be fixed soon? In its current state, Superset charts and datasets don’t work with any Postgresql columns of type “timestamp with time zone”.

I can replicate this issue using a fresh install of the latest docker image per the instructions here.

Screenshot below.

According to a pip freeze in the app container (superset_app), this version is using psycopg2 v. 2.9.1.

Manually replacing version 2.9.1 with 2.8.6 as suggested above resolves the issue.

@mail2lawi’s solution above (time at time zone ‘CEST’)… also was an effective fix, leading me to support the hypothesis that this issue is specifically related to columns which contain timestamptz values.

Thank you for your help all!

This library (psycopg2-binary) was not installed at all.

1. pip uninstall -y psycopg2-binary
2. pip install psycopg2-binary==2.8.6

@santicomp2014,

You should just be able to:

* Clone the repo:  $ git clone https://github.com/apache/superset.git

* $ cd superset/requirements/

* Change both **docker.txt** and **development.txt** to _psycopg2-binary==2.8.6_

* Run: $ docker-compose -f docker-compose-non-dev.yml up

For me, this didn’t work. I found this “hack” : https://github.com/apache/superset/issues/15768#issuecomment-987887089

    docker-compose -f docker-compose-non-dev.yml up
    docker-compose -f docker-compose-non-dev.yml exec superset pip uninstall -y psycopg2-binary
    docker-compose -f docker-compose-non-dev.yml exec superset pip install psycopg2-binary==2.8.6
    docker-compose -f docker-compose-non-dev.yml restart superset

As a work-around I convert the date to a string. So instead of…

select rfq._created_dt, ...

use…

select to_char(rfq._created_dt, 'YYY-MM-DD') as _created_dt, ...

The actual error does not seem to come from the Postgres server as indicated by the error message. I did some debugging, and it turns out that in the condition… if abs(minutes) >= 1440: … minutes is a datetime.timedelta object which can’t be evaluated against and int using the ‘>=’ operator. But exception shows up as if it were coming from postgres.

String solution does not work well, it will cause other problems with some visualizations. The real issue is that (with Postgres) dates can be missing the timezone in some way, and forcing a timezone into the output field fixes the date issue properly without making it a string. Making the main temporal date field into a string, will cause other problems.

So, for date fields, don’t use… select date_fld, ... Add the timezone to the field… select date_fld::timestamptz at time zone 'EST',

I have 2 superset’s connected in same database.

Node 1:

Node 2: Query error: Query 56: <class 'TypeError'> 2021-07-19 17:06:23,715:ERROR:superset.sql_lab:Query 56: <class 'TypeError'> postgresql error: '>=' not supported between instances of 'datetime.timedelta' and 'int' 2021-07-19 17:06:23,840:WARNING:superset.views.base:postgresql error: '>=' not supported between instances of 'datetime.timedelta' and 'int'

This error appears when there is a column of type date/timestamp, when I remove this column it works