我正在尝试从 ORACLE 表中检索其中一列中有昨天日期的记录。我有一份工作,每天都在只检索昨天的记录。
注** 列insert_date的类型为 date
到目前为止,这是我拥有的两个SQL语句:
SELECT distinct column1
FROM table1
WHERE flag = 'N'
AND insert_date BETWEEN TRUNC(CURRENT_DATE-1) AND TRUNC(CURRENT_DATE)
AND ipaddress IS NOT NULL
AND ipaddress <> '0.0.0.0';
和
SELECT distinct column1
FROM table1
WHERE flag = 'N'
AND insert_date
BETWEEN To_Timestamp(CONCAT (to_char(CURRENT_DATE-1),' 12:00:00 AM'))
AND To_Timestamp(Concat (to_char(CURRENT_DATE-1),' 11:59:59 PM'))
AND ipaddress IS NOT NULL
AND ipaddress <> '0.0.0.0';
似乎这两个 SQL 语句产生相同的输出。但是,我不是 ORACLE 方面的专家,所以我想问问社区是否有任何我不知道的"陷阱"。
使用介于,但从结束日期中减去一秒。
insert_date between trunc(CURRENT_DATE-1) and trunc(CURRENT_DATE) - 1/86400
to_Timestamp(Concat (to_char(CURRENT_DATE-1),' 12:00:00 AM'))
是非常多余的,我认为这就是另一种方法的原因(trunc(current_date-1)
)
我能看到的唯一"陷阱"是日期正好为上午 12:00:00 的记录将包含在第二个查询中,而不是第一个查询中。
有一种非常简单的方法来检查此类任何问题:
SELECT distinct column1
FROM table1
WHERE flag = 'N' AND insert_date between trunc(CURRENT_DATE-1)
and trunc(CURRENT_DATE) and ipaddress is not null and ipaddress<>'0.0.0.0';
MINUS
SELECT distinct column1
FROM table1
WHERE flag = 'N'
AND insert_date between To_Timestamp(Concat (to_char(CURRENT_DATE-1),' 12:00:00 AM')) and To_Timestamp(Concat (to_char(CURRENT_DATE-1),' 11:59:59 PM'))
and ipaddress is not null and ipaddress<>'0.0.0.0';
和
SELECT distinct column1
FROM table1
WHERE flag = 'N'
AND insert_date between To_Timestamp(Concat (to_char(CURRENT_DATE-1),' 12:00:00 AM')) and To_Timestamp(Concat (to_char(CURRENT_DATE-1),' 11:59:59 PM'))
and ipaddress is not null and ipaddress<>'0.0.0.0';
MINUS
SELECT distinct column1
FROM table1
WHERE flag = 'N' AND insert_date between trunc(CURRENT_DATE-1)
and trunc(CURRENT_DATE) and ipaddress is not null and ipaddress<>'0.0.0.0';
如果从这些查询中的任何一个获得结果,则意味着两种方法并不完全相等。
试试这个:
SELECT distinct column1
FROM table1
WHERE flag = 'N' AND
insert_date = trunc(sysdate-1,'DD')
and ipaddress is not null and ipaddress<>'0.0.0.0';
您的第一个查询工作正常,但当您想要筛选一天的数据时,您可能不需要使用 between
。