Conditional logic for working hours, except launch break

Hi,

I’m trying to user conditional logic for between 9 to 17 except launch break which is beetween 12:30 to 13:30. I used hour selection and other functions but I couldn’t find right function or way to write exception for launch break. I need your about this issue.

SQL query like below

SELECT case

/*working hours and above 1.5gbps - OK*/
when (TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("09:00:00","%H:%i:%s") AND TIME_FORMAT("12:30:00","%H:%i:%s") 
		OR TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("13:30:00","%H:%i:%s") AND TIME_FORMAT("18:00:00","%H:%i:%s")) AND (rxrate > 1500) AND WEEKDAY(NOW()) < 5 then "0"

/*working hours - below 1.3gbps WARNING*/
when (TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("09:00:00","%H:%i:%s") AND TIME_FORMAT("12:30:00","%H:%i:%s") 
		OR TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("13:30:00","%H:%i:%s") AND TIME_FORMAT("18:00:00","%H:%i:%s")) AND (rxrate < 1300) AND WEEKDAY(NOW()) < 5 then "1"		

/*Launch Break - below 1gbps OK*/
when (TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("12:30:00","%H:%i:%s") AND TIME_FORMAT("13:30:00","%H:%i:%s")) AND (rxrate < 1200) AND WEEKDAY(NOW()) < 5 then "4"						

/*working hours - below 1gbps CRITICAL*/
when (TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("09:00:00","%H:%i:%s") AND TIME_FORMAT("12:30:00","%H:%i:%s") 
		OR TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("13:30:00","%H:%i:%s") AND TIME_FORMAT("18:00:00","%H:%i:%s")) AND (rxrate < 1000) AND WEEKDAY(NOW()) < 5 then "5"		
ELSE '7'
	END AS "NS_RX_MESAI_KONTROL",
	rxrate as "RX"
FROM throughput`Preformatted text`

WHERE $__timeFilter(logtimestamp)

my flux query like below.

import "date"
import "system"
from(bucket: "myBucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "mydevicesmeasurements")
  |> filter(fn: (r) => r["devices"] == "device01" or r["devices"] == "device02")
  |> filter(fn: (r) => r["_field"] == "rxmbitsrate")
  //|> filter (fn: (r) => string(v: date.weekDay(t: r._time)) !~ /(0|6)/)
  |> aggregateWindow(every: 30s, fn: mean, createEmpty: false)
  |> pivot(columnKey: ["devices"], rowKey: ["_time"] , valueColumn: "_value" )
  |> map(fn: (r) => ({r with _value: r["device01"] + r["device02"] }))
  |> drop(columns: ["device01","device02","env"])

// I had to add +3 because of our timezone
  |> map (
	fn: (r) => ({r with
	  level: 	if r._value > 1000 and (((date.hour(t:r._time) + 3) < 18) and ((date.hour(t:r._time) + 3) > 9)) then 
					"workins hours OK" 
				else if r._value < 1000  and (((date.hour(t:r._time) + 3) > 17) or ((date.hour(t:r._time) + 3) < 9)) then
					"out of office"
				else
					"OTHER",
				}),
		 )  

I need some help understanding your query.

Why is _value being used in combination with _time in your map? Does it matter what the _value is in determining if the level should be “working hours OK” or “OTHER”?

To allow for a lunch break, I’d suggest checking for that first in your if/else block:

if (lunch break hours range)
  "lunch break"
else if (working hours range)
  "working hours"
else
  "out of office"

That way if it’s during lunch hours it’ll return the first value “lunch break” and skip the rest.

You probably want to use < 17 and >= 9 here. < 17 because 17:01 is after hours and 9:01 is in working hours

I couldn’t get right query for launch break actually. I used “between” and “time_format” in my SQL query but couldn’t find any solution for flux equivalent. I tried hourSelection function but there is no option for minutes.

how can I create flux logic like below?

if ( current_time  between 09:00 and 12:30)
 "morning working hours"
else if ( current_time  between 12:30 and 13:30)
 "launch break"
else if ( current_time  between 13:30 and 18:00)
 "afternoon working hours"
else
 "other"

Is there any solution for that ?

is there any function like SQL time_format in flux?