Using postgres and our data is in GMT so we routinely have to change to Eastern. Same query but one has where created = now() - 6 months and other is field filter. The further out we go, the data is correct but using 'today' or 'yesterday' and we get a difference in numbers between reports by about 50. The field filter is either pulling in earlier data or not pulling in enough data (say not bringing in dates after 8 pm).
Is this a timezone issue? We are using date_trunc('month'...) for the queries so could that be affecting today/yesterday?
If we do a date filter and choose last month plus checkbox of current month or last 7 days including today then it's accurate.
Just wanted to know why the relative date throws things off as does leaving the "include Today/This month" check box unchecked
Yes, I had read that already. Both reports are using ::timestamptz at time zone 'US/Eastern' so they're both exactly the same. The only difference is one has no filter and is using where created_at = now() - 6 months and other is using field filter.
The field filter is almost always high or low for only 'yesterday' or 'today' but is accurate the further one goes out. Seems the problem is the filter and not the query which is why I came here.
I don't understand #8 in guide you gave me
If by this point you have the correct value, it means your time zone was converted by the number of hours forward or backwards you manually set the filter. If that’s the case, check whether the offset you’ve come up with matches either the time zone of the data warehouse or the timezone of Metabase itself.
What does it mean by manually set the filter?
What timezone is metabase pulling and why would it pull differently if I'm using ::timestamptz?