0

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.

Nomad
  • 250
  • 3
  • 11
  • 27
  • What data type is `entry_date`? Unless it's `TIMESTAMP WITH TIME ZONE` I'm not sure why you need to do adjustment? – Alex Poole Feb 26 '14 at 20:14
  • It is timestamp with timezone :/ I figured it out though thank you! – Nomad Feb 26 '14 at 20:17
  • 1
    OK, good 8-) But if your session is in the same timezone as the data you're querying, you *might* find it less confusing to use [`current_date` or `current_timestamp` instead of `sysdate`](http://stackoverflow.com/a/17925834/266304). Might give you fewer problems around DST changes too, perhaps. – Alex Poole Feb 26 '14 at 20:24
  • Ah, nice! Thanks for the tip, I've used current_date, but hasn't worked nice to know! – Nomad Feb 26 '14 at 20:25

1 Answers1

2

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
D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • Well the first query appears good, BUT when I run the second one I get entries from the first queries specified time range. Hmm, I'm just attempting to load everything as PST. I may be skewering the results with my odd where clause. – Nomad Feb 26 '14 at 20:03
  • Response to your Updated answer : Effectivley I'm looking for 6AM - 6PM Local time. And the second query, 7PM - 3AM. – Nomad Feb 26 '14 at 20:04
  • Going to try your answer. – Nomad Feb 26 '14 at 20:04
  • 1
    No, I think your time math is just off. Which is understandable because mine was off twice while writing an answer :) – D Stanley Feb 26 '14 at 20:06
  • Hmm, that gives me results ranging from 5am - 10am as opposed to 7pm - 3am. – Nomad Feb 26 '14 at 20:06
  • Your last posted answer looks correct, the math is throwing me off, should I throw away the select -8/24 I have? I currently have it it looks currect. – Nomad Feb 26 '14 at 20:08
  • Would this work for 6am - 6pm? SELECT to_char(entry_date -8/24, 'DD-MON-YYYY HH12:MI:SSam') date_time, OLD_BIN_ID old_bin, NEW_BIN_ID NEW_BIN, ISBN ASIN, QUANTITY FROM BINEDIT_ENTRIES --WHERE ENTRY_DATE BETWEEN trunc(SYSDATE -1) +06/24 AND trunc(SYSDATE -1) +16/24 WHERE ENTRY_DATE BETWEEN trunc(SYSDATE) -1 +06/24 + 8/24 AND trunc(SYSDATE) -1 +16/24 + 8/24 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; – Nomad Feb 26 '14 at 20:10
  • No that would give you 6 AM to _4_ PM _local_ (assuming you are 8 hours behind UTC). for 6 PM you'd need to add 18/24 on the right-hand side (12 + 6) – D Stanley Feb 26 '14 at 20:13
  • Haha doy!! Sorry working on two different buildings one is 8 hour schedules one is 10s. Let me check this looks right, if so, I'll mark your answer correct. – Nomad Feb 26 '14 at 20:16
  • What would be the process for say 18:30/24? – Nomad Feb 26 '14 at 20:34
  • 1
    That would be `18.5/24` – D Stanley Feb 26 '14 at 20:35