Date Functions Trunc (SysDate)-CodePudding

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.

CodePudding user response：

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...}

CodePudding user response：

It sounds like you have a column that is of data type TIMESTAMP and you only want to select rows where that TIMESTAMP indicates that it is today's date? And as a related problem, you want to find those that are the current month, based on some system values like CURRENT TIMESTAMP and CURRENT DATE? If so, let's call your column TRANSACTION_TIMESTAMP instead of (reserved word) DATE. Your first query could be:

SELECT COUNT(NUM)
FROM TABLE
WHERE
STATUS = 'CLND'
AND
DATE(TRANSACTION_TIMESTAMP)=CURRENT DATE

The second example of finding all for the current month up to today's date could be:

SELECT COUNT(NUM)
FROM TABLE
WHERE
STATUS = 'CLND'
AND
YEAR(DATE(TRANSACTION_TIMESTAMP)=YEAR(CURRENT DATE) AND
MONTH(DATE(TRANSACTION_TIMESTAMP)=MONTH(CURRENT DATE) AND
DAY(DATE(TRANSACTION_TIMESTAMP)<=DAY(CURRENT DATE)