我写这行是为了将日期和时间从int数据类型组合到时间戳:
(to_timestamp(cast(theDate* 1000000 + theTime as varchar2(255)), 'YYYYMMDDHH24MIss'))
我需要把它添加到where子句中,只得到日期+时间小于的5小时的地方
当前日期,为了更清楚,我需要查询只显示sysdate上最后5小时的日期。
类似这样的东西:
Where hour((to_timestamp(cast(theDate* 1000000 + theTime as varchar2(255)), 'YYYYMMDDHH24MIss'))) - hour(sysdate) < 5
如果我可以在oracle上使用datediff,我会使用这个例子:
其中datediff('HOUR',(imrecorddate,imrecordtime(,sysdate(<5
我想得到一些帮助,因为我在网上找不到。
谢谢。
如果您需要最后五个小时,那么只需使用日期/时间比较。假设没有未来的日期/时间:
where (to_timestamp(cast(theDate* 1000000 + theTime as varchar2(255)), 'YYYYMMDDHH24MIss')) >= current_timestamp - interval '5' hour
让我们假设您有一个表:
CREATE TABLE table_name (
thedate INT,
thetime INT
);
(这是一种糟糕的做法,您应该只使用一个支持相同日期和时间组件的DATE
列。(
样本数据:
INSERT INTO table_name ( thedate, thetime )
WITH dates ( dt ) AS (
SELECT SYSDATE - INTERVAL '04:59:58' HOUR TO SECOND FROM DUAL UNION ALL
SELECT SYSDATE - INTERVAL '04:59:59' HOUR TO SECOND FROM DUAL UNION ALL
SELECT SYSDATE - INTERVAL '05:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT SYSDATE - INTERVAL '05:00:01' HOUR TO SECOND FROM DUAL
)
SELECT TO_CHAR( dt, 'YYYYMMDD' ), TO_CHAR( dt, 'HH24MISS' ) FROM dates;
然后你可以做:
SELECT *
FROM table_name
WHERE TO_DATE( thedate||TO_CHAR(thetime, 'FM000000'), 'YYYYMMDDHH24MISS' )
>= SYSDATE - INTERVAL '5' HOUR;
哪个会输出:
THEDATE THETIME 20210604 20210604 90320 20210604 90319