Does Metabase automatically converts the time to session time while running a native query?

I use MySQL and yesterday I noticed that there was a difference in my values while running through native query (we store data in UTC) so to convert it in our timezone I use convert_tz but while running building the same query using the Custom editor (Metabase converts it into the session time - which is correct).

As soon as I removed the convert_tz from my native query the values matched.

So is it true that Metabase automatically runs the native query in the session timezone?

Hi @testout
Yes, if you define Report Timezone, then it sends the following together with your query:
SET @@session.time_zone = '<your-report-timezone>'

So basically using convert_tz in native query is useless to convert to your session timezone? right?

@testout Well, no, it will just be compared to the Report Timezone.

I dont understand. So given that whenever I write something from custom question and check its SQL it shows that it has converted into session timezone. So does the same happens when I run the query in Native editor without convert_tz from my end?

@testout If you define Report Timezone in Admin > Settings > Localization, then for MySQL, it will set the session before each query. That was the answer to your question.

Timezone are difficult - very difficult - and everything can quickly become really complicated, when mixing things.

1 Like

Understood :smile: Thank you so much!