DQL interval - Filtering working hours

[helton_harada]

In DQL, how to filter only working hours in a timeseries? For example: requests count from 8h to 18h (desconsidering 19h to 7h)

[helton_harada]

For example, this is our DQL usecase where we need to apply this kind of filter....

timeseries count = sum(dt.service.request.count, filter: { ( in(dt.entity.service, classicEntitySelector("type(service),entityName.startsWith(\"service1-")"))) OR ( in(dt.entity.service, classicEntitySelector("type(service),entityName.startsWith(\"service2-")")))
OR ( in(dt.entity.service, classicEntitySelector("type(service),entityName.startsWith(\"service3-")"))) }), by: { dt.entity.service }, interval:1h//, from:"08:00", to:"20:00"
| fieldsAdd entityName(dt.entity.service)
| sort count desc

[Julius_Loman · Accepted solution]

A native DQL solution (I changed the filter not to use entity selector):

timeseries {
  requests = sum(dt.service.request.count), timestamp=start()}
  , by:{ dt.entity.service}
  , filter:{ startsWith(entityName(dt.entity.service), "service1") or startsWith(entityName(dt.entity.service), "service2") or startsWith(entityName(dt.entity.service), "service3") }
  , interval:1h
| fieldsAdd dt.entity.service.name = entityName(dt.entity.service)
| fieldsAdd requests=if(getDayOfWeek(timestamp[])<6, requests[])
| fieldsAdd requests=if(getHour(timestamp[],timezone:"Europe/Vienna")>=8, requests[])
| fieldsAdd requests=if(getHour(timestamp[],timezone:"Europe/Vienna")<=18, requests[])
| fieldsRemove timestamp

[helton_harada]

Very Good @JuliusLoman !

It works! Thanks a lot for your help!