Run search only on data for certain time of day

[heilman]

Hello,

I am attempting to run a search that will only include data occurring before 6 AM or after 6 PM, then group the data by 12 hour time range and country, and sum the total amount for each time period. For example, I want the sum of the data for each country in time periods such as Monday 6 PM - Tuesday 6 AM, Tuesday 6 PM - Wednesday 6 AM, etc.

This is the code I have:

host=10 AND (date_hour<6 OR date_hour>18) | bucket _time span=12h | stats sum(AMOUNT) as total by COUNTRY _time

This query is limiting my data to only those that occured before 6 AM or after 6 PM, but it reporting is as Monday 8 PM - Tueday 8 AM (with the data between Tuesday 6 AM-8AM excluded), Tuesday 8 AM - Tuesday 8 PM (with the data between T 8 AM and 6 PM excluded), etc. Is there any way so that the reported time frame is from 6 to 6, instead of from 8 to 8?

Thank you.

[woodcock]

If you search is really correct (highly questionable, IMHO, because the date* fields are pre-TZ-normalized fields), then you can shift the time like this:

host=10  | eval _time=_time - 2*60*60 | eval date_hour=strftime(_time, "%H") | where (date_hour<6 OR date_hour>18) | bucket _time span=12h | stats sum(AMOUNT) as total by COUNTRY _time

This recalculates date_hour to be sure it is correct (and so you may not even need the time shift, because this was probably your original problem).