My database values are in UTC. I have created at column that is in UTC. I am based in UTC + 10 timezone. I am using the native Metabase question to build a report.
I want to get a count of records created by day until today in relative criteria. I have converted the created at by creating a custom column and adding + 10 hours. It shows the correct value in the results.
But it does not work as expected in the criteria and still working in UTC timezone. I know I can do this in SQL, but I want to stick to native Metabase as much as I can.
So the question is do the custom column after converting to + 10 UTC work based on the original UTC database value or the new converted value?
For the Metabase version, you can get it by clicking on the upper right pull down menu (either the Metabase logo or the 4 squares), then select Help → About Metabase.
MySQL has timezone aware data types, so order of operations is important. Metabase might try to do the counts first, then apply the time offset, which will generate the wrong data. If you can read it, take a look at the generated SQL. If that’s happening then try creating a Model with the offset column, then do the counts in a question using the Model as the data souce. That should keep Metabase from trying to optimize the query by reversing the operations.
SELECT
DATE(__mb_source.Invoice Date) AS Invoice Date,
COUNT(*) AS count
FROM
(
SELECT
CAST(
DATE_ADD(invoices.created_at, INTERVAL 10 hour) AS date
) AS Invoice Date
FROM invoices
WHERE
CAST(
DATE_ADD(invoices.created_at, INTERVAL 10 hour) AS date
) BETWEEN DATE(DATE_ADD(NOW(6), INTERVAL -30 day))
AND CAST(DATE(NOW(6)) AS datetime)
AND (invoices.invoicestype = 'Renewal')
) AS __mb_source
GROUP BY
DATE(__mb_source.Invoice Date)
ORDER BY
DATE(__mb_source.Invoice Date) ASC
I think I managed to resolve that by changing the selection criteria to original non converted created at date and applying the changed custom column that is UTC + 10 hours to only grouping the count.