Discrepancy between results on a Database tool and ReTool with same query

Queries and Resources
1

Hi All!

I was hoping you could help.

I am just running a basic query on Retool to show sales growth MoM, I'm currently bumping into an issue - the same query gives me different results on Dbeaver (the DB Tool I am using) and ReTool. The differences are not massive but it still bothers me that the same query results in two different numbers.

Any tips on how to resolve this / ideas of what could be causing it?

Kind regards,
Cristina

2

Hi,

That’s odd as queries are run serverside. What is the query?

4 
SELECT

date_trunc('month',a.created_at)::DATE "date",

SUM((a.original_amount)::float/100),

count(distinct a.id) "booking_count"

from

booking_activitybooking a

LEFT JOIN

marketplace_activity act on a.activity_id = act.id

LEFT JOIN

marketplace_supplier s on act.supplier_id = s.id

group by

date_trunc('month',a.created_at)::DATE

This is the query! Just a standard one really

5

That is a bit strange - any chance that the Retool query is caching the results and showing older data?

6

That shouldn't be the issue as the discrepancy is the same even if I re-run the query or create a new query with the same code without caching

7

What is the discrepancy? In the sum or the count?

8

Google suggests that the use_server_time_zone option in DBeaver may be the culprit here.
If the timezone is different between client and server then your date_trunc functions may be the discrepancy.

9

Thank you! Turns out Dbeaver had the wrong timezone, and so does Retool :upside_down_face:.
I have finally managed to change the one in DBeaver but can't see a way to set a default timezone in ReTool - is that correct?

10

You could try using
SET SESSION timezone TO 'YOUR REGION GOES HERE';
before running your query, seems to work on my retool db and returns a timezone offset, eg:
SET SESSION timezone TO 'America/Denver';
SELECT EXTRACT(TIMEZONE FROM now());
Hope this helps
image