Have a problem with time filter (inside dbxquery and in search of Splunk)- Why am I not getting an alert during time?

[Julia1231]

Hi,

I get data from DB using dbxquery. I set the time filter by: 

WHERE time BETWEEN DATE_TRUNC('hour',NOW()) - INTERVAL '4 HOURS' AND DATE_TRUNC('hour',NOW()) - INTERVAL '2 HOURS'

I use DATE_TRUNC in order to get data from exact hour (7:00-9:00, insteads of 7:10-9:10 or example)

After that, using Splunk, I make a span = 2h

In the alert, I want to send it every 2 hours.

There is a problem from 4:00 - 6:00 but at 9:30, I don't receive any alert (because there is nothing return from the search)

However, now, at 10:10, when I run the search, it sort the result that I want.

_time	id	count
2022-10-14 04:00	123	0
2022-10-14 06:00	123	0

Effectively, there is no data for id "123" in the filter period in SQL query. 

Do you have any idea how can I do it more generally, not filter time like what I am doing now in the SQL, to avoid this problem? Or a way to filter time by Splunk, not by SQL

Here is my search:

|dbxquery connection="database" query="
SELECT id as id, time as time, count(*) as count
FROM table
WHERE time BETWEEN DATE_TRUNC('hour',NOW()) - INTERVAL '4 HOURS' AND DATE_TRUNC('hour',NOW()) - INTERVAL '2 HOURS'
GROUP BY id, time"
|lookup lookup.csv id OUTPUT id 
|eval list_id = "123,466,233,111"
|eval split_list_id= split(list_id ,",")
|mvexpand split_list_id
|where id=split_list_id
|eval _time=strptime(time,"%Y-%m-%dT%H:%M:%S.%N")
|timechart span=2h count by id
| untable _time id count
| makecontinuous
| where count = 0
|stats max(_time) as date_time by id
|eval date_time=strftime(date_time,"%Y-%m-%dT%H:%M:%S")

[Julia1231]

What I found. Because there is no data for the id "123" in the period of DATE_TRUNC('hour',NOW()) - INTERVAL '4 HOURS' AND DATE_TRUNC('hour',NOW()) - INTERVAL '2 HOURS', so the following script doesn't work for it.
I update my question