How to count events from 5 pm today to 5 am the next day?

[auaave]

Hi Guys,

Our operations changed their schedule from 5 pm to 5 am. How can I count events from these times daily?

I tried this with last 24 hours but it doesn't work.

| eval hour=strftime(_time, "%H")
| search hour>=6
| timechart SPAN=1H COUNT AS IDEVENT 
| rename IDEVENT AS " PALLET QUANTITY"

Thanks!

[auaave]

Thank you @DalJeanis!! This is great! 🙂

[DalJeanis · Accepted solution]

Your test for hour>6 will get 7 am to just less than 12 midnight.

You could change that to (hour<6 OR hour>15), but there is a much better option.

Try this ...

 | bin _time span=1h
 | stats count as PalletQuantity by _time 

Now you have your Pallet Quantities for every hour of every day. To get 5 pm to 5 am, we can either do a complicated comparison, or we can add 7 hours and do a simple one. (Guess which one I prefer?)

 | rename COMMENT as "add 7 hours in a junk field, throw away any full days, then keep only hours that are less than noon"  
 | eval killtime =  (_time+7*3600) % 86400
 | where killtime <  43200

We've already spanned it at the hour level, so timechart has very little to do here.

| timechart span=1h sum(PalletQuantity) as PalletQuantity

The above logic will work exactly the same if you bin the data at the 1m, 5m, 10m or 15m level, which would allow some level of zooming. You should do that if your operational data is accurate at a higher resolution.

---

Note - Please get out of the habit of using spaces inside field names. Replace your spaces with underscores or use CamelCase, and you will save yourself loads of headaches. If for organizational reasons you cannot present information this way, then do your field renaming at the very end, after all calculations are complete.

[harishalipaka]

convert your date field to epoch than add index=_internal earliest=1510101000 latest= 1510129800
ealist=from 5 pm epochs
latest=to 5 AM epoc

[auaave]

Thanks a lot! 🙂
This works too!