Hi,
If you would consider this one, maybe not too difficult.
Simply to have Hour as a pulse send option. Ideally between the hours of x and y.
I would use this to pushhourly sales reports for my restaurant POS system.
thanks for consideration
Chuck
Hi @flamber,
there is no provision to upvote as you have mentioned.
I looked and looked cannot find the icon
Chuck
Hi @flamber,
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.
Chuck
@crwheelr
Iâm a little confused. Are you using 0.33.0-RC1? And you write âalertsâ and then âpulsesâ - which ones are you using?
Hi @flamber,
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.
@crwheelr
Okay, then itâs definitely a timezone issue.
Have a look at the troubleshooting guide: https://metabase.com/docs/latest/troubleshooting-guide/timezones.html
But I donât think SQLite support timezones, so I think it just uses whatever timezone Metabase is being executed in (which would be Java, the Docker container or VM, the host OS)
i am trying to set the java timezone. would be best if this could just inherit from windows if windows is the OS. an any case statemennt
java -Duser.timezone= America/Tijuana is not recognized.
is there a command line statement that will inherent the windows timezone?
do you see any error in my statement above?
thanks
Chuck
@crwheelr
I donât know much about Windows, but try something like this:
on the first post:
java -Duser.timezone="America/Tijuana"
Which version of Java?
But if the timezones are not honored in SQLite, then you would have to convert it with your queries. You can make a quick check by doing a query like this:
SELECT current_date, current_time
EDIT: You can convert the timestamp columns with this:
SELECT DATETIME(your_time_column, 'localtime')
Java version 1.6.0_221, the latest I believe.
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
Charles
@crwheelr
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.
charles
@crwheelr
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
correction, the way to invoke java / metabase instance is as follows:
-Duser.timezone=UTC -jar metabase.jar
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â);
@crwheelr
Yes, like I already posted:
SELECT DATETIME(your_time_column, 'localtime')
If you do not add , 'localtime', then it just uses the timezone of SQLite, which is probably 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.
Charles