检索从前一天的6pm到今天的6pm的数据 [英] Retrieving data from 6pm previous day till 6pm present day

问题描述

我有一个表的列"create_date".我想选择在前一天下午6点到今天下午6点之后插入的记录.每天都需要获取此数据，而无需手动输入日期和时间. 因此，任何人都可以帮助我编写查询以获取符合此条件的记录. 列数据类型为日期.

I have one table with column "create_date". I want to select records which are inserted after 6 pm previous day till 6pm present day.This data is to be fetched everyday w/o need to manually enter date and time. So can anyone help me to write a query to fetch records which falls under this criteria. Column data type is Date.

谢谢.

推荐答案

对于前一天的下午6点，您可以使用TRUNC(SYSDATE -1)，它将把时间部分截断为00:00:00，然后增加18小时.同样，对于今天，请执行TRUNC(SYSDATE)并增加18小时.

For previous day 6PM, you could use TRUNC(SYSDATE -1), which will trunc the time portion to 00:00:00 and then add 18 hours. Similarly, for present day ,do TRUNC(SYSDATE) and add 18 hours.

SQL> SELECT to_char(trunc(SYSDATE -1) + 18/24,'mm/dd/yyyy hh24:mi:ss')
  2  FROM dual;

TO_CHAR(TRUNC(SYSDA
-------------------
02/25/2015 18:00:00

SQL>

因此， trunc(SYSDATE -1)+ 18/24 为您提供昨天的日期为下午6点.

So, trunc(SYSDATE -1) + 18/24 gives you yesterday's date as 6PM.

测试用例:

SQL> WITH DATA AS
  2    ( SELECT 'text' col, SYSDATE create_date FROM dual
  3    )
  4  SELECT *
  5  FROM DATA
  6  WHERE create_date
  7  BETWEEN (TRUNC(sysdate -1) + 18/24) AND (TRUNC(sysdate) + 18/24)
  8  /

COL  CREATE_DA
---- ---------
text 26-FEB-15

SQL>

这篇关于检索从前一天的6pm到今天的6pm的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！