编写SQL查询以返回有关日期和时间的适当记录。
编写SQL查询以返回有关日期和时间的适当记录。
提问于 2022-03-12 10:29:00
我需要一个查询来过滤出日期和时间的记录。它是一个Oracle数据库，下面是表结构和很少的示例记录：
结构：
DATE_AND_TIME is Date and NOT NULL
DATE_AND_TIME is VARCHAR2 (8 char)
KEY is VARCHAR2 (256 Char)示例：
DATE_AND_TIME DATE_AND_TIME_24HOUR_FORMAT KEY
10-MAR-22 16:05:00 500014
07-MAR-22 11:02:20 500013
07-MAR-22 11:00:24 500012
04-MAR-22 12:14:50 500011
04-MAR-22 12:11:06 500010
04-MAR-22 12:02:18 500009
04-MAR-22 11:52:45 500008示例查询：
select DATE_AND_TIME,DATE_AND_TIME_24HOUR_FORMAT,KEY
from PRCI_AUDIT_DETAILS
where DATE_AND_TIME_24HOUR_FORMAT > '12:02:18'
and DATE_AND_TIME_24HOUR_FORMAT < '11:20:40'
and DATE_AND_TIME > '04-MAR-22'
and DATE_AND_TIME < '08-MAR-2022'
order by DATE_AND_TIME DESC我需要查询，比如今天是12-3月-2022年和16:00。它应该从11-3月-2022 16:50:58到12-2022 15:40:00返回记录。
注意:这是客户端表；不能更改结构。
回答 1
Stack Overflow用户
发布于 2022-03-12 14:38:02
您应该不要将日期/时间值存储为字符串，使用正确的DATE或TIMESTAMP数据类型。而且，您应该永远不要将字符串与DATE值进行比较。
在您的示例中，11-DEC-2022将先于11-MAR-2000，因为D将先于M。'08-MAR-2022'先于'08-MAR-29'
列DATE_AND_TIME的数据类型为DATE。DATE值始终包含日期和时间部分。也许在你的例子中，时间部分只是00:00:00。
你说，你不能改变结构-和你的客户谈谈！解决方法可以是添加一个虚拟列：
alter table PRCI_AUDIT_DETAILS ( REAL_DATE_AND_TIME DATE generated always as (
TO_DATE(
TO_CHAR(DATE_AND_TIME, 'YYYY-MM-DD"T"') || DATE_AND_TIME_24HOUR_FORMAT
DEFAULT NULL ON CONVERSION ERROR,
'YYYY-MM-DD"T"HH24:MI:SS')
) virtual )或者创建一个视图：
create view v_PRCI_AUDIT_DETAILS as
select t.*,
TO_DATE(
TO_CHAR(DATE_AND_TIME, 'YYYY-MM-DD"T"') || DATE_AND_TIME_24HOUR_FORMAT
DEFAULT NULL ON CONVERSION ERROR,
'YYYY-MM-DD"T"HH24:MI:SS') AS REAL_DATE_AND_TIME
from PRCI_AUDIT_DETAILS t;然后，您可以在您的选择中使用它：
select DATE_AND_TIME,DATE_AND_TIME_24HOUR_FORMAT,KEY
from PRCI_AUDIT_DETAILS
where REAL_DATE_AND_TIME > TO_DATE('04-MAR-22 12:02:18', 'DD-MON-RR HH24:MI:SS', 'NLS_DATE_LANGUAGE = American')
and REAL_DATE_AND_TIME < TO_DATE('08-MAR-2022 11:20:40', 'DD-MON-YYYY HH24:MI:SS', 'NLS_DATE_LANGUAGE = American')注意RR和YYYY的不同格式，否则您可能会陷入Y2K-问题
否则，如果无法更改结构，则需要将转换放入查询中。
select DATE_AND_TIME,DATE_AND_TIME_24HOUR_FORMAT,KEY
from PRCI_AUDIT_DETAILS
where TO_DATE(
TO_CHAR(DATE_AND_TIME, 'YYYY-MM-DD"T"') || DATE_AND_TIME_24HOUR_FORMAT
DEFAULT NULL ON CONVERSION ERROR,
'YYYY-MM-DD"T"HH24:MI:SS') > TO_DATE('04-MAR-22 12:02:18', 'DD-MON-RR HH24:MI:SS', 'NLS_DATE_LANGUAGE = American')
and TO_DATE(
TO_CHAR(DATE_AND_TIME, 'YYYY-MM-DD"T"') || DATE_AND_TIME_24HOUR_FORMAT
DEFAULT NULL ON CONVERSION ERROR,
'YYYY-MM-DD"T"HH24:MI:SS') < TO_DATE('08-MAR-2022 11:20:40', 'DD-MON-YYYY HH24:MI:SS', 'NLS_DATE_LANGUAGE = American')或者使用CTE高速公路：
WITH cte as (
select t.*,
TO_DATE(
TO_CHAR(DATE_AND_TIME, 'YYYY-MM-DD"T"') || DATE_AND_TIME_24HOUR_FORMAT
DEFAULT NULL ON CONVERSION ERROR,
'YYYY-MM-DD"T"HH24:MI:SS') AS REAL_DATE_AND_TIME
from PRCI_AUDIT_DETAILS t)
select DATE_AND_TIME,DATE_AND_TIME_24HOUR_FORMAT,KEY
from cte
where REAL_DATE_AND_TIME > TO_DATE('04-MAR-22 12:02:18', 'DD-MON-RR HH24:MI:SS', 'NLS_DATE_LANGUAGE = American')
and REAL_DATE_AND_TIME < TO_DATE('08-MAR-2022 11:20:40', 'DD-MON-YYYY HH24:MI:SS', 'NLS_DATE_LANGUAGE = American')对我来说，你所说的“如果今天是12-3月-2022和16:00的话，你说的是什么意思不清楚。它应该从11-3月-2022 16:50:58返回记录到12-3月-2022 15:40。”
如果没有进一步的信息，我只能猜测。可能是其中之一：
- 来自
SYSTIMESTAMP - INTERVAL '1' DAY + INTERVAL '50:58' MINUTE TO SECOND - 来自
SYSTIMESTAMP - INTERVAL '23:09:02' HOUR TO SECOND - 转到
SYSTIMESTAMP - INTERVAL '20' MINUTE
其中包括：
select DATE_AND_TIME,DATE_AND_TIME_24HOUR_FORMAT,KEY
from PRCI_AUDIT_DETAILS
where REAL_DATE_AND_TIME > `SYSTIMESTAMP - INTERVAL '23:09:02' HOUR TO SECOND
and REAL_DATE_AND_TIME < `SYSTIMESTAMP - INTERVAL '20' MINUTE页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71448898
复制相关文章
相似问题
腾讯云开发者
