Result discrepancy between postgres client and metabase table (timezone issue)

Get Help
1

Does table view do anything special to the data when a question is specified as SQL ?

I have a question defined as

SELECT name, AVG(value) FROM (SELECT sensors.name, ((MAX(value) - MIN(VALUE)) * 10.0) as value FROM sensors
    LEFT JOIN sensor_values_cleaned ON sensors.id=sensor_values_cleaned.sensor_id
    WHERE sensors.name ILIKE 'bi1%_water' AND ts >= (NOW() + INTERVAL '-7 day')::timestamptz GROUP BY 1, ts::date) AS f
    GROUP BY 1 ORDER BY 1

If I run this in metabase I get a table result with the first rows to be

Name Avg
bi1600004_water 663.75
bi1600005_water 585
bi1600007_water 320
bi1600010_water 250
bi1600012_water 626.25
bi1600013_water 413.75
bi1600014_water 418.75

If I copy and paste the same query into datagrip (a postgres client) I get the results

bi1600004_water,528.75
bi1600005_water,492.5
bi1600007_water,247.5
bi1600010_water,212.5
bi1600012_water,523.75
bi1600013_water,322.5
bi1600014_water,360

Why the discrepancy ?

2

Ok I have to convert to utc before casting to date. Ie

SELECT name, MAX(max_ts), AVG(value) FROM (SELECT sensors.name, MAX(ts) as max_ts, ((MAX(value) - MIN(VALUE)) * 10.0) as value FROM sensors
    LEFT JOIN sensor_values_cleaned ON sensors.id=sensor_values_cleaned.sensor_id
    WHERE sensors.name ILIKE 'bi1%_water' AND ts >= (NOW() + INTERVAL '-7 day')::timestamptz GROUP BY 1, (ts at time zone 'UTC')::date) AS f
    GROUP BY 1 ORDER BY 1
3

Bingo, time zone issues are a particularly nasty kind of bugs. Was first thing I suspected when I saw your query as well. Metabase docs has a nice trobleshooting guide section on the topic: