使用 sysdate 的时间范围内的 Oracle SQL WHERE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22051906/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL WHERE within a time range using sysdate
提问by Spartacus38
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.
我正在尝试从前一天选择特定时间范围内的数据，但我可能会错误地计算我的 where 子句。我试过切换时间等。基本上我想查看早上 6 点到下午 6 点的所有数据，然后是晚上 7 点到凌晨 3 点，但我的结果并没有反映出来。我试过 trunc(sysdate)-1 '00:00:00'<- 但指定了时间，但我觉得我对这个函数不够熟悉。
Note: DB is in UTC hence the 8/24.
注意：DB 是 UTC，因此是 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:
结果：
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:
结果：
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.
正如您所看到的，它似乎没有查看 where 子句，我相信我的格式不正确，我通常只查看昨天的整体，而不是时间范围，所以这是我第一次尝试这样做。谢谢你。
回答by D Stanley
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.
实际上，您要求的是当地时间上午 8 点到下午 4 点之间的所有内容。我说早上 8 点，因为您在 WHERE 子句中添加了 16 小时并在 SELECT 子句中减去了 8。
If you meantto query between 7 PM local time and 3AM you would just add 8 hours in the WHERE clause:
如果您打算在当地时间晚上 7 点到凌晨 3 点之间查询，您只需在 WHERE 子句中添加 8 小时：
WHERE ENTRY_DATE BETWEEN
trunc(SYSDATE) -1 +19/24 + 8/24
AND trunc(SYSDATE) -1 +27/24 + 8/24
