Date Functions Trunc (SysDate)

I am running the below query to get data recorded in the past 24 hours. I need the same data recorded starting midnight (DATE > 12:00 AM) and also data recorded starting beginning of the month. Not sure if using between will work or if there is better option. Any suggestions.

SELECT COUNT(NUM)
FROM TABLE
WHERE
STATUS = 'CNLD'
AND
TRUNC(TO_DATE('1970-01-01','YYYY-MM-DD') + OPEN_DATE/86400) = trunc(sysdate)

Output (Just need Count). OPEN_DATE Data Type is NUMBER. the output below displays count in last 24 hours. I need the count beginning midnight and another count starting beginning of the month.

The query you've shown will get the count of rows where OPEN_DATE is an 'epoch date' number representing time after midnight this morning*. The condition:

TRUNC(TO_DATE('1970-01-01','YYYY-MM-DD') + OPEN_DATE/86400) = trunc(sysdate)

requires every OPEN_DATE value in your table (or at least all those for CNLD rows) to be converted from a number to an actual date, which is going to be doing a lot more work than necessary, and would stop a standard index against that column being used. It could be rewritten as:

OPEN_DATE >= (trunc(sysdate) - date '1970-01-01') * 86400

which converts midnight this morning to its epoch equivalent, once, and compares all the numbers against that value; using an index if there is one and the optimiser thinks it's appropriate.

To get everything since the start of the month you could just change the default behaviour of trunc(), which is to truncate to the 'DD' element, to truncate to the start of the month instead:

OPEN_DATE >= (trunc(sysdate, 'MM') - date '1970-01-01') * 86400

And the the last 24 hours, subtract a day from the current time instead of truncating it:

OPEN_DATE >= ((sysdate - 1) - date '1970-01-01') * 86400

db<>fiddle with some made-up data to get 72 back for today, more for the last 24 hours, and more still for the whole month.

Based on your current query I'm assuming there won't be any future-dated values, so you don't need to worry about an upper bound for any of these.

_{*Ignoring leap seconds...}