I am using the beta version now as it has improved time filtering. However I discovered this apparent bug as follows:
I have setup 5 alerts to run at various hours of the business day, with intent to email the sales figure. All five pulses are identical excepting for the time of the pulse issuance. The first pulse is sent with accurate figure, but the rest scheduled for on the hour later in the day all display zero sales. The filter used is order_date = today, and then sum up order_total. pulsing that same filter throughout the day should yield cumulative sales for the day.
I have diagnosed this further. version is 0.33 just downloaded few days ago.
The daily sailes coming up empty is not a problem with the pulse report, not using alerts either.
Its simply the question. i am looking at orders table with order date of “current” as described in the pulldown.
if i choose previous day, figures comes out correct. last week just fine.
Its an issue with Current Day" specifically.
If i issue the query at 4PM i get the correct value for today’s sales. if I run the query at 5PM zero is the result.
your java statement recommendation is not working either.
your suggested SELECT current_date/time revealed that sqlite thinks the time is 7 hours off.! not i am confused. this has nothing to do with Java I presume then.
I don’t think that i want to be editing timestamp fields as this is all within the POS vendor’s control and i dont want to break anything. let me research this further with vendor.
thanks for support
Interesting. I didn’t even know that Metabase could run on Java 6. You might want upgrade to Java 8 or 11.
Looking a bit further on SQLite, it does not support timezones, so you can store the data in UTC or “localtime”.
It sounds like the program is storing in UTC, so you might need to run Metabase in UTC too - not sure what other consequences that might create.
You should not edit any timestamps without talking to the vendor, but you can do whatever you want in Metabase, since it’s read-only.
i flipped Metabase to timezone UTC, consistent with sqlite. now my noon pulse triggered is the 7PM one. ARGH.
can you tell me if the admin:timezone function is actually setting the java user.timezone class?
perhaps the better solution where i don’t have to offset all my pulses in time is to set the Java class.
Well, all your data is in SQLite, which does not support timezones - and because of that, Metabase has difficulties playing nice with that.
The Metabase reporting timezone is controlling the schedule, if I remember correctly.
So maybe your solution would be to run everything as UTC.
Otherwise you could setup an intermediate database like Postgres, which pulls in the data from your SQLite, and do whatever conversions you need there, and then setup Metabase to get the data from Postgres.
I am pretty stumped, let me give you the results if timezone settings
The two knobs I can adjust are the java instance timezone which can be set by the statement
java -Duser.timezone=UTC for example
The other knob is setting the MB timezone. here are the resutls
setting java to PST and MB to PST - the pulse kicks off at the correct time, the sum of sales is the correct valuer, but the time displayed in the order time field is 8 hours AFTER the correct time
setting java to UTC and MB to PST - the results are same as above, however the pulse did not trigger, i had to run it manually
setting java to UTC and MB to UTC - the results are same as above, however the pulse did not trigger, i had to run it manually
If metabase/java cannot by properly synchronized with sqlite, perhaps sqlite time-based queries can have the additional param utc included? example: SELECT something, something_else FROM table_tbl WHERE time_paid > datetime(‘2019-08-08 00:00:00’, ‘utc’);
I want to try this. i only have a few questions involving time of day so editing SQL may be the way… in the latest .33 beta version, is is possible to have MB generate an SQL query that I can then modify in your suggested fashion?
unrelated topic regarding beta version: its very hard to find the admin screen, perhaps having something queing off of the M logo would be logical. just mentioning this.