Hourly pulses

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

@crwheelr
Go and upvote by clicking :+1: on the first post:

1 Like

Hi @flamber,

there is no provision to upvote as you have mentioned.

I looked and looked cannot find the icon
Chuck

@crwheelr
I guess you’re not logged in on Github?

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)

@flamber

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:

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

  1. 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

  2. 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

  3. 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

@crwheelr
And what about “setting java to PST and MB to UTC”?
EDIT: CST=UTC

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