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?
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.
Understood Thank you so much!