舍入到前 2 小时窗口 Oracle SQL

Question

我正在尝试将日期（日期数据类型也包括时间戳）舍入到前 2 小时块。 例如 13：23 -> 12：00， 18：12 -> 18：00

我让它使用 MOD 函数在 MySQL 中工作，如下所示：

DATE_ADD(DATE(DATE_ADD(created_at, INTERVAL - 7 HOUR)), INTERVAL HOUR(DATE_ADD(created_at, INTERVAL - 6 HOUR)) - MOD(HOUR(DATE_ADD(created_at, INTERVAL - 6 HOUR)), 2) HOUR) AS Window_Start

**增加了复杂性，因为我也在移动时间 a） 以校正 7 小时的时区差异和 b） 因为我需要在分组之前将时间偏移 1 小时。但这不是问题所在。

但我无法让它在 Oracle 平台上工作。具体来说，我似乎无法将时间的小时提取为输入 MOD（） 的数字。我一直在尝试使用 CAST，TO_TIMESTAMP和TO_CHAR但似乎没有任何效果。通常的错误是"数据类型不一致"。

EXTRACT 仅适用于时间戳类型，不适用于日期。TO_TIMESTAMP仅适用于字符串。但是 EXTRACT（TO_TIMESTAMP（TO_CHAR（） 也不起作用。

我相信有一种更简单的方法可以做到这一点...

Answer 1

在 Oracle 中，一种方法是使用日期算术。 例如：

select date '2000-01-01' + floor((sysdate - date '2000-01-01') * (24 / 2)) / (24 / 2)
from dual;

"24"表示一天中的几个小时。 "2"表示要截断到的两个小时时间段。

Answer 2

我不确定这是答案，但它会给你一个兔子洞来追逐：

select sysdate as curr_dt, to_date(to_char(sysdate - interval '2' hour, 'yyyy-mm-dd HH24')) as two_hours_ago from dual;

希望对您有所帮助！

Answer 3

要以数字格式获取小时，这将起作用。

从双中选择TO_NUMBER（TO_CHAR（SYSDATE，'HH'））

您可以使用此日期格式 DD/MM/YYYY HH：MI：SS 的时间戳部分单独选择每个片段。

Answer 4

前段时间我写了这个泛型函数：

CREATE FUNCTION MakeInterval(ts IN TIMESTAMP, roundInterval IN INTERVAL DAY TO SECOND) RETURN TIMESTAMP DETERMINISTIC IS
    denom INTEGER;
BEGIN
    IF roundInterval >= INTERVAL '1' HOUR THEN
        denom := EXTRACT(HOUR FROM roundInterval);
        IF MOD(24, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts) + TRUNC(EXTRACT(HOUR FROM ts) / denom) * denom * INTERVAL '1' HOUR;
    ELSIF roundInterval >= INTERVAL '1' MINUTE THEN
        denom := EXTRACT(MINUTE FROM roundInterval);
        IF MOD(60, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts, 'hh') + TRUNC(EXTRACT(MINUTE FROM ts) / denom) * denom * INTERVAL '1' MINUTE;
    ELSE
        denom := EXTRACT(SECOND FROM roundInterval);                
        IF MOD(60, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts, 'mi') + TRUNC(EXTRACT(SECOND FROM ts) / denom) * denom * INTERVAL '1' SECOND;
    END IF;
END MakeInterval;

在您的情况下，用法将是

SELECT MakeInterval(created_at, INTERVAL '2' HOUR) 
FROM ...

但也许这在您的情况下是矫枉过正，没有该功能的用法将是：

SELECT TRUNC(created_at) + TRUNC(EXTRACT(HOUR FROM created_at) / 2) * 2 * INTERVAL '1' HOUR
FROM ...