Problem with time variable(timezone)

For example,
when I query my data using MySQL Workbench, a time variable looks like
2015-09-22 10:43:30 (and this is correct in my timezone(KST)),

but when using Metabase(question written in SQL), it looks like
2015-09-22T01:43:30.000Z

Those results are from the same query. So it must be the same, but as you can see, they’re different. Metabase one is 9 hours before Workbench one. All of the time variables that I have in my DB face the same problem. How can I solve this?

I changed the report timezone to my timezone in Admin Panel - Settings - General, but it didn’t work.

The string you’re seeing - ‘2015-09-22T01:43:30.000Z’ is the representation in UTC which is 9 hours off, so technically it’s correct but not rendering the time in your local time zone.

What do you get if you look at the bare rows of the table? Is that displayed in KST or GMT?

To make things clear first, I’m not a database administrator, and not very good at SQL. I’m in marketing team and we’re trying to use Metabase as a simple BI tool without bothering our developers. However, at some point, we had to use the SQL editor, so I’m working on it.

Here are some query results:

select @@system_time_zone
‘UTC’

select @@global.time_zone, @@session.time_zone
‘UTC’, ‘Asia/Seoul’

select @@time_zone
‘Asia/Seoul’

Are these informations sufficient to answer your question?

Yes, so what is happening is that for SQL queries we are not formating the date strings that come back automatically.

For queries that are executed through our GUI, we do a bit of post processing depending on context to produce a format that is useful.

For SQL queries we pass the data that the database returns without any modification. In this case, the database is storing your time with the GMT (+0) timezone.

I’ve opened an issue at https://github.com/metabase/metabase/issues/2053

In the meantime, you’d need to set an explicit timezone in your SQL query unfortunately.

Out of curiosity, what are you trying to do in SQL? We’re working on making the GUI more powerful and it would be useful for us to know what you’re trying to do.

Simplified example

Table 1 : user id, product id, amount
Table 2 : product id, product name, region id
Table 3 : region id, region name, is_overseas

There’s no way to directly connect Table 1 & 3. We use SQL queries to join those tables and get purchase data of each region.

In addition, sometimes we need calculated fields. We also use SQL queries in that situation.

Hi Dave, sounds like your question is unrelated to the original topic of this thread, but in any case you are correct that the GUI query interface is limited in the couple ways you describe right now.

We are continually working on enhancing the product to support more querying scenarios like the ones you describe, so so keep an eye out and I’m sure you’ll see these situations covered in the near future. Until then you can always fall back to writing SQL queries directly, so that’s what I’d recommend in these cases for now.

Yup, I was just asking what he was trying to do in SQL in the above comment :wink:

As @agilliland mentioned, we’re working on a way for you to do the example you mentioned without needing SQL. Stay tuned =)