Get Current Datetime with Timezone Presto

1

Hello,

I'm using Presto with Metabase and I'm trying to get the current timestamp but I need the timestamp in my timezone.

Im following this presto Docs:
https://prestodb.io/docs/current/functions/datetime.html

My presto java timezone is set to UTC and I fill the env JAVA_TIMEZONE on metabase with UTC too.
My reporting metabase timezone was set to database timezone and I tried put UTC too but nothing changed.

I run the following command:
select current_timestamp AT TIME ZONE 'America/Sao_Paulo'

but is the same result as
select current_timestamp

so timezone is not working.

On presto cli, that command worked fine.

02
023016×886 300 KB

Could you help me to solve this problem?

Thank you

2

I think metabase is converting the query result to a different timezone, is it possible?

select created_datetime_utc AT TIME ZONE 'America/Sao_Paulo', created_datetime_utc from hive.refined_table limit 10

For example if I set metabase to database timezone, this query didn't work because metabase is converting to another timezone than America/Sao_Paulo

3

Hi @rubenssoto
Like I already mentioned, please check your database query log, so you can see the difference between Metabase and DBeaver - all the way from the session start until the actual query.

4

On presto side the query is the same, something is happening on metabase side.

37
373009×445 62.8 KB
24
243576×1606 174 KB
40
403009×965 263 KB

5

@rubenssoto

  1. Use DBeaver, not cli.
  2. Capture all the queries from the beginning. Like I said, there's session variables set.
6

In dbeaver I get the right result too.

41
413180×2056 598 KB

I dont understand when you said capture all the queries from the beginning. I see only one query when I execute on metabase.

16
162980×1352 330 KB

7

@rubenssoto So did you also start DBeaver in UTC? And are you running Metabase on the same machine as DBeaver? If not, then try that.

You'll need to look at the database response, so you can figure out if it is a querying issue or a frontend issue.

Like I already said, timezones are very complicated, and would require a lot of debugging to track down where the problem is.

There's several known issues: https://github.com/metabase/metabase/issues?q=is%3Aopen+is%3Aissue+label%3AMisc%2FTimezones