oracle 基于当前日期和时间的条件查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6040640/
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
Conditional querying based on the current date & time
提问by Suresh Chaganti
I have an sql query in which I need to select the records from table where the time is between 3:00 PM yesterday to 3:00 PM today iftoday's time is more than 3:00 PM.
我有一个 sql 查询，如果今天的时间超过下午 3:00 ，我需要从表中选择时间在昨天下午 3:00 到今天下午 3:00 之间的记录。
If today's time is less than that, like if today's time is 1:00 PM. then then my query should take today's time as 1:00 PM (which should return me records).
如果今天的时间小于该时间，例如今天的时间是下午 1:00。那么我的查询应该将今天的时间设为下午 1:00（应该返回我的记录）。
I need to get the time between 3:00pm yesterday to 3:00pm today if todays time is more than 3:00pm if todays time is less than 3:00pm then get the 3:00pm yesterday to current time today
如果今天的时间超过下午 3:00，我需要获取昨天下午 3:00 到今天下午 3:00 之间的时间，如果今天的时间小于下午 3:00，则获取昨天下午 3:00 到今天的当前时间
回答by OMG Ponies
The best way of handling this is to use an IF statement:
处理此问题的最佳方法是使用IF 语句：
IF TO_CHAR(SYSDATE, 'HH24') >= 15 THEN
SELECT x.*
FROM YOUR_TABLE x
WHERE x.date_column BETWEEN TO_DATE(TO_CHAR(SYSDATE -1, 'YYYY-MM-DD')|| ' 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD')|| ' 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
ELSE
SELECT x.*
FROM YOUR_TABLE x
WHERE x.date_column BETWEEN TO_DATE(TO_CHAR(SYSDATE -1, 'YYYY-MM-DD')|| ' 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND SYSDATE
END IF;
Conditional WHERE clauses are non-sargable.
条件 WHERE 子句是不可 sargable 的。
Previously:
之前：
If I understand correctly, you want to get records within the last day. If the current time is 3 PM or later, the time should be set to 3 PM. If earlier than 3 PM, take the current time...
如果我理解正确，您希望获得最后一天内的记录。如果当前时间为下午 3 点或更晚，则应将时间设置为下午 3 点。如果早于下午 3 点，请取当前时间...
SELECT x.*
FROM YOUR_TABLE x
JOIN (SELECT CASE
WHEN TO_CHAR(SYSDATE, 'HH24') >= 15 THEN
TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD')|| ' 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
ELSE SYSDATE
END AS dt
FROM DUAL) y ON x.date_column BETWEEN dt - 1 AND dt
Note:
笔记：
dt - 1means that 24 hours will be subtracted from the Oracle DATE.
dt - 1意味着将从 Oracle DATE 中减去 24 小时。
Reference:
参考：
回答by APC
There is no need for an IF statement. This can be solved easily with simple SQL.
不需要 IF 语句。这可以通过简单的 SQL 轻松解决。
My table T23 has some records with dates; here is a sample with times at 3.00pm:
我的表 T23 有一些带日期的记录；这是下午 3 点时间的示例：
SQL> select id, some_date from t23
2 where to_char(some_date,'HH24') = '15'
3 /
ID SOME_DATE
---------- ---------
14 16-MAY-11
38 17-MAY-11
62 18-MAY-11
81 19-MAY-11
SQL>
As the current time is before 3.00pm my query will return records from 17-MAY and 18-MAY but not the record where ID=62...
由于当前时间在下午 3 点之前，我的查询将返回 17-MAY 和 18-MAY 的记录，但不会返回 ID=62...
SQL> select to_char(sysdate, 'DD-MON-YYYY HH24:MI') as time_now
2 from dual
3 /
TIME_NOW
-----------------
18-MAY-2011 10:45
SQL> select id, to_char(some_date, 'DD-MON-YYYY HH24:MI') as dt
2 from t23
3 where some_date between trunc(sysdate-1)+(15/24)
4 and least( trunc(sysdate)+(15/24), sysdate)
5 /
ID DT
---------- -----------------
38 17-MAY-2011 15:00
39 17-MAY-2011 16:00
40 17-MAY-2011 17:00
41 17-MAY-2011 18:00
42 17-MAY-2011 19:00
43 17-MAY-2011 20:00
44 17-MAY-2011 21:00
45 17-MAY-2011 22:00
46 17-MAY-2011 23:00
47 18-MAY-2011 00:00
48 18-MAY-2011 01:00
49 18-MAY-2011 02:00
50 18-MAY-2011 03:00
51 18-MAY-2011 04:00
52 18-MAY-2011 05:00
53 18-MAY-2011 06:00
54 18-MAY-2011 07:00
55 18-MAY-2011 08:00
56 18-MAY-2011 09:00
57 18-MAY-2011 10:00
20 rows selected.
SQL>
