Timezone handling in Druid driver

[moumny]

There are a couple of issues Druid driver handling of timezones.

Currently queries the dates in UTC. For example when querying for today the intervals are "intervals":["2016-09-23T00:00:00.000Z/2016-09-24T00:00:00.000Z"]

The second issue is with displaying the timestamps in the table view for example.
In HEAD The timeseries query display the time in local time (configured in the admin panel) while the GroupBy displays is in UTC. In 0.19.3 the timestamps are displayed in UTC.
Not sure why this happens the proper timezone is included in the druid query in both cases and the output look the same.

[jesusrtc]

+1

This problem comes from the query since
SELECT now()-now()::date
Is shown always like if we were in UTC never mind the timezone (configured in the admin panel)
It's like if you had put UTC as default like SET TIMEZONE TO 'UTC' before executing query.

This works correctly on OS X.

Postgres (both)
Elastic beanstalk
Metabase 0.19.3

[jesusrtc]

I run the same query on Metabase and directly on the DB and I get different results.

[camsaul]

@jesusrtc this issue is about the Druid database, but it sounds like you're talking about using Postgres.

[camsaul]

@moumny did the two PRs you opened resolve this, or are there still more timezone issues?

[jesusrtc]

@camsaul
Yes, do I have to create a new ticket?

The problem is that it seems the query runs on a different time zone, for example when I run:

SELECT count(*)
FROM table t
WHERE t.created_at::date=now()::date

When I run it at 8:00PM in SF it shows:
PGadmin: X -- Right number
Metabase: 0 -- We have not data from tomorrow

[camsaul]

@jesusrtc, yes you should open a different issue because this issue is talking about Druid, not Postgres. It will make it hard for other people to find our discussion if it's in a place nobody would think to look 😉

Check out our guide to handling timezones. I'm guessing you just need to set the Report Timezone setting in the admin panel. If that doesn't work, please open a new issue 👍

[moumny]

The last timezone issue was fixed by c45e9ee (workaround removal) !
Altough it would make sense to have more timezone tests ^^

Anyway, thanks to @camsaul and @tlrobinson for your help !