Timezone offset doesn't work for filtering and grouping in PgSQL

[qeorqe]

• 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

0.20.4

Expected results

When I set the Offset (timezone) in the table properties,
I expect that all queries for this table will operate with timestamps in UTC+offset TZ, including:

• filtering by time;
• grouping by time;
• displaying time in results.

Actual results

Configuration:

• UTC+3 settings for my PostgreSQL DB and my PC;
• Offset = 3 for table in Superset;
• date column in epoch_ms format.

Only the display of the timestamp in results is correct in my configuration.
Filtering and grouping doesn't consider the Offset (look below for more info).

Steps to reproduce

Let's filter by date from 2015-09-21 00:00 to 2015-09-22 00:00 and group it by day.
The query will be:

SELECT 
  DATE_TRUNC('hour', (timestamp 'epoch' + (date/1000.0) * interval '1 second')) AS __timestamp,
  COUNT(*) AS count
FROM orders
WHERE date >= 1442793600000.0
  AND date <= 1442880000000.0
GROUP BY DATE_TRUNC('day', (timestamp 'epoch' + (date/1000.0) * interval '1 second'))

Timestamps are displayed correctly (UTC+3) in Table View, but we have two problems.

1. Filtering
Timestamps in results are between 2015-09-21 03:00 UTC+3 and 2015-09-22 03:00 UTC+3
This part of code doesn't consider the Offset:

WHERE date >= 1442793600000.0
AND date <= 1442880000000.0

2. Grouping
This part also doesn't consider the Offset and returns timestamp in UTC, while I need UTC+3:

timestamp 'epoch' + (date/1000.0) * interval '1 second'

I see a couple of possible solutions for the grouping problem:

1. Always use SET timezone = {{offset}} before the query;
2. Always add offset to the date in the query.

[mistercrunch]

Warning: this issue has been inactive for 181 days and will be automatically closed on 2018-05-01 if there is no further activity. You can add the keepup label to the issue in order for this bot to keep it open.

[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.

[maltitco]

@qeorqe Have you found what is the reason that the offset does not work?