Oracle 获取前一天的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2555392/
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 get previous day records
提问by Phill Pafford
Ok I think I'm getting the previous year instead of the previous day, but I need to previous day.
好的，我想我得到的是前一年而不是前一天，但我需要前一天。
SELECT TO_DATE(TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'),'YYYY-MM-DD') - 1 FROM Dual
I'm comparing it to a datetime stamp in this format and wish to get all the rows from the previous day.
我将它与这种格式的日期时间戳进行比较，并希望获取前一天的所有行。
YYYY-MM-DD HH:MM:SS
So I'm trying something like this
所以我正在尝试这样的事情
SELECT field,datetime_field
FROM database
WHERE datetime_field > TO_DATE(TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'),'YYYY-MM-DD') - 1
回答by Eric Schneider
how about sysdate?
系统日期怎么样？
SELECT field,datetime_field
FROM database
WHERE datetime_field > (sysdate-1)
回答by Ronnis
You can remove the time part of a date by using TRUNC.
您可以使用 删除日期的时间部分TRUNC。
select field,datetime_field
from database
where datetime_field >= trunc(sysdate-1,'DD');
That query will give you all rows with dates starting from yesterday. Note the second argument to trunc(). You can use this to truncate any part of the date.
该查询将为您提供所有日期从昨天开始的行。注意 的第二个参数trunc()。您可以使用它来截断日期的任何部分。
If your datetime_fied contains '2011-05-04 08:23:54', the following date will be returned
如果您的 datetime_fied 包含'2011-05-04 08:23:54'，则将返回以下日期
trunc(datetime_field, 'HH24') => 2011-05-04 08:00:00
trunc(datetime_field, 'DD') => 2011-05-04 00:00:00
trunc(datetime_field, 'MM') => 2011-05-01 00:00:00
trunc(datetime_field, 'YYYY') => 2011-00-01 00:00:00
回答by ronaldo
this
这个
SELECT field,datetime_field
FROM database
WHERE datetime_field > (sysdate-1)
will work. The question is: is the 'datetime_field' has the same format as sysdate ? My way to handle that: use 'to_char()' function (only works in Oracle).
将工作。问题是：'datetime_field' 的格式是否与 sysdate 相同？我的处理方法是：使用“to_char()”函数（仅适用于 Oracle）。
samples: previous day:
样品： 前一天：
select your_column
from your_table
where to_char(sysdate-1, 'dd.mm.yyyy')
or
或者
select extract(day from date_field)||'/'||
extract(month from date_field)||'/'||
extract(year from date_field)||'/'||
as mydate
from dual(or a_table)
where extract(day from date_field) = an_int_number and
extract(month from date_field) = an_int_number and so on..
comparing date:
比较日期：
select your_column
from your_table
where
to_char(a_datetime_column, 'dd.mm.yyyy') > or < or >= or <= to_char(sysdate, 'dd.mm.yyyy')
time rangebetween yesterday and a day before yesterday:
昨天和前天之间的时间范围：
select your_column
from your_table
where
to_char(a_datetime_column, 'dd.mm.yyyy') > or < or >= or <= to_char(sysdate-1, 'dd.mm.yyyy') and
to_char(a_datetime_column, 'dd.mm.yyyy') > or < or >= or <= to_char(sysdate-2, 'dd.mm.yyyy')
other time rangevariation
其他时间范围变化
select your_column
from your_table
where
to_char(a_datetime_column, 'dd.mm.yyyy') is between to_char(sysdate-1, 'dd.mm.yyyy')
and to_char(sysdate-2, 'dd.mm.yyyy')
回答by shahkalpesh
SELECT field,datetime_field
FROM database
WHERE datetime_field > (CURRENT_DATE - 1)
Its been some time that I worked on Oracle. But, I think this should work.
我在 Oracle 工作已经有一段时间了。但是，我认为这应该有效。
回答by Omari Victor Omosa
Simple solution and understanding
简单的解决方案和理解
To answer the question:
要回答这个问题：
SELECT field,datetime_field
FROM database
WHERE TO_CHAR(date_field, 'YYYYMMDD') = TO_CHAR(SYSDATE-1, 'YYYYMMDD');
Some explanation
一些解释
If you have a field that is not in date format but want to compare using date i.e. field is considered as date but in number formate.g. 20190823 (YYYYMMDD)
如果您的字段不是日期格式但想使用日期进行比较，即字段被视为日期但采用数字格式，例如 20190823 (YYYYMMDD)
SELECT * FROM YOUR_TABLE WHERE ID_DATE = TO_CHAR(SYSDATE-1, 'YYYYMMDD')
If you have a field that is in date/timestamp format and you need to compare, Just change the format
如果您有一个日期/时间戳格式的字段并且您需要比较，只需更改格式
SELECT TO_CHAR(SYSDATE-1, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
IF you want to return it to date format
如果您想将其恢复为日期格式
SELECT TO_DATE(TO_CHAR(SYSDATE-1, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS NEW_DATE FROM DUAL
Conclusion.
结论。
With this knowledge you can convert the filed you want to compare to a YYYYMMDD or YYYY-MM-DD or any year-month-date format then compare with the same sysdate format.
有了这些知识，您可以将要比较的文件转换为 YYYYMMDD 或 YYYY-MM-DD 或任何年-月-日格式，然后与相同的 sysdate 格式进行比较。
回答by Prashant
I think you can also execute this command:
我想你也可以执行这个命令：
select (sysdate-1) PREVIOUS_DATE from dual;
从双中选择 (sysdate-1) PREVIOUS_DATE；
回答by K142146 Syed Wasey Saeed
Im a bit confused about this part "TO_DATE(TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'),'YYYY-MM-DD')". What were you trying to do with this clause ? The format that you are displaying in your result is the default format when you run the basic query of getting date from DUAL. Other than that, i did this in your query and it retrieved the previous day 'SELECT (CURRENT_DATE - 1) FROM Dual'. Do let me know if it works out for you and if not then do tell me about the problem. Thanks and all the best.
我对“TO_DATE(TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'),'YYYY-MM-DD')”这部分有点困惑。你想用这个条款做什么？当您运行从 DUAL 获取日期的基本查询时，您在结果中显示的格式是默认格式。除此之外，我在您的查询中执行了此操作，它检索了前一天“SELECT (CURRENT_DATE - 1) FROM Dual”。请让我知道它是否适合您，如果没有，请告诉我这个问题。谢谢，祝一切顺利。
