CODEWITHSUNDEEP
sqloracle-databasedatetimewhere-clause
Nomad
Nomad

Reputation: 268

Oracle SQL WHERE within a time range using sysdate

I'm trying to select data from the previous day, and within a certain time frame, but I may be calculating my where clause incorrectly. I've tried switching times around etc. Basically I want to see all data from 6am-6pm, and then 7pm-3am, but My results aren't relecting such. I've tried between trunc(sysdate)-1 '00:00:00'<- but specifying the time, but I feel I'm not familiar enough with the function.

Note: DB is in UTC hence the 8/24.

Query:

--TOTAL PROBLEM STOW EVENTS
SELECT to_char(entry_date -8/24, 'DD-MON-YYYY HH12:MI:SSam'), OLD_BIN_ID old_bin, NEW_BIN_ID NEW_BIN, ISBN ASIN, QUANTITY 
FROM BINEDIT_ENTRIES
WHERE ENTRY_DATE BETWEEN trunc(SYSDATE) -1 +4/24 AND trunc(SYSDATE) -1 +16/24
--where entry_date BETWEEN trunc(sysdate)-1 '00:00:00' AND trunc(sysdate)-1 '00:00:00.000'
AND substr(old_bin_id,1,2) = 'SC'
AND substr(new_bin_id,1,2) = 'vt'
GROUP BY ENTRY_DATE, OLD_BIN_ID, NEW_BIN_ID, ISBN, Quantity
ORDER BY QUANTITY DESC;

Result:

enter image description here

This appears to look correct, BUT when I change to look at other time range, it shows me this..

Second Query(Night Time):

--TOTAL PROBLEM STOW EVENTS
SELECT to_char(entry_date -8/24, 'DD-MON-YYYY HH12:MI:SSam'), OLD_BIN_ID old_bin, NEW_BIN_ID NEW_BIN, ISBN ASIN, QUANTITY 
FROM BINEDIT_ENTRIES
WHERE ENTRY_DATE BETWEEN trunc(SYSDATE) -1 +16/24 AND trunc(SYSDATE) -1 +24/24
--where entry_date BETWEEN trunc(sysdate)-1 '00:00:00' AND trunc(sysdate)-1 '00:00:00.000'
AND substr(old_bin_id,1,2) = 'SC'
AND substr(new_bin_id,1,2) = 'vt'
GROUP BY ENTRY_DATE, OLD_BIN_ID, NEW_BIN_ID, ISBN, Quantity
ORDER BY QUANTITY DESC;

Result:

enter image description here

As you can see it doesn't appear to be looking at the where clause, I believe I have it formatted incorrectly, I typically just look at yesterday as a whole, and not a time range, so this is my first time attempting this. Thank you.

Upvotes: 0

Views: 16004

Answers (1)

D Stanley
D Stanley

Reputation: 152626

Effectively you're asking for everything between 8 AM and 4 PM local time. I say 8 AM since you're adding 16 hours in the WHERE clause and subtracting 8 in the SELECT clause.

If you meant to query between 7 PM local time and 3AM you would just add 8 hours in the WHERE clause:

WHERE ENTRY_DATE BETWEEN 
                   trunc(SYSDATE) -1 +19/24 + 8/24
               AND trunc(SYSDATE) -1 +27/24 + 8/24

Upvotes: 2

Related Questions