Different results of SQL on Metabase viz. my server

FAQ
1

Objective: To find the hits received by hour of day

Here is the code I'm running:

SELECT 
     hour (
            convert_tz(created_at, '+00:00','+05:30')
          ) AS hour_extract
   , count(*) AS hit_count 
 
FROM hits
WHERE date(created_at) > (CURDATE() - INTERVAL 360 DAY)
GROUP BY 1  
ORDER BY 1 ASC;

So basically I'm grouping all hits received by "created_at" - which is a date/time field.
Example: 2017-12-07 12:03:32

But the result of the same query is different when I run it on my server (with Workbench/DBviz.) as compared to metabase.

Have a look here:

temp11

And it's not like I'll find the value 801 (from cell B2) anywhere in column C. The values just don't match up.

Any idea why this is the case?
I would have thought that a query in metabase is the same as running it on my Mysql server.

thanks!

2

Hi,
I have a very similar issue but in my data the solution is that in our SQL server we normalised time zones to have comparable data.
So the field “created_at_local” always gives me the german time of the event, even if the event was created in another time zone.
But metabase is (unlike the old frontend of my sql server) “clever” and would be able to normalise the data itself and does that as well. So I end up having data that are again not matching up.

Not sure if I explained that correctly. Is the sum of a month for example the same in both environments?

Hope you find the issue,
Eva

3

Hi Eva,

The sum is almost the same. Discrepancy of < 1%.
So it's clear that Metabase is doing something to the timestamp.

Here is a comparison (Metabase in orange).
X axis = time
Y axis = hits
Metabase is wrong, but the manual data pull from SQL is correct and makes sense.
nonat