将日期四舍五入为 10 分钟间隔

Question

我有一个 DATE 列，我想在查询中舍入到下一个较低的 10 分钟间隔（请参见下面的示例）。

我设法通过截断秒然后减去分钟的最后一位数字来做到这一点。

WITH test_data AS (
        SELECT TO_DATE('2010-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2010-01-01 10:05:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2010-01-01 10:09:59', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2010-01-01 10:10:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2099-01-01 10:00:33', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
)
-- #end of test-data
SELECT
  d, TRUNC(d, 'MI') - MOD(TO_CHAR(d, 'MI'), 10) / (24 * 60)
FROM test_data

这是结果：

01.01.2010 10:00:00    2010年1月1日 10:00:00
01.01.2010 10:05:00    2010年1月1日 10:00:00
01.01.2010 10:09:59    2010年1月1日 10:00:00
01.01.2010 10:10:00    2010年1月1日 10:10:00
01.01.2099 10:00:33    01.01.2099 10:00:00

按预期工作，但有更好的方法吗？

编辑：

我对性能很好奇，所以我用 500.000 行和（不是真正的）随机日期进行了以下测试。我将把结果作为评论添加到所提供的解决方案中。

DECLARE
  t       TIMESTAMP := SYSTIMESTAMP;
BEGIN
  FOR i IN (
    WITH test_data AS (
      SELECT SYSDATE + ROWNUM / 5000 d FROM dual
      CONNECT BY ROWNUM <= 500000
    )
    SELECT TRUNC(d, 'MI') - MOD(TO_CHAR(d, 'MI'), 10) / (24 * 60)
    FROM test_data
  )
  LOOP
    NULL;
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t );
END;

此方法花费了 03.24 s。

Answer 1

select
  trunc(sysdate, 'mi')
  - numtodsinterval(mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10), 'minute')
from dual;

甚至

select
  trunc(sysdate, 'mi')
  - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10) / (24 * 60)
from dual;

Answer 2

我通常讨厌约会 ->字符->不需要时进行日期转换。我宁愿用数字。

select trunc((sysdate - trunc(sysdate))*60*24,-1)/(60*24)+trunc(sysdate) from dual;     

这会提取当天的分钟数，将其截断为 10 分钟间隔，然后将其添加回去以使其再次成为日期。当然，您可以将 sysdate 替换为您想要的任何日期。它比我想要的更信任隐式转换，但至少它适用于任何 NLS 日期格式。

Answer 3

您可以将返回的值作为字符串，并将左侧的子串到最后一分钟的数字，并将其替换为 0。除非你提供某种指标，否则我不会说那更好。

Answer 4

不一定更好，但还有另一种方法：

WITH test_data AS (
        SELECT TO_DATE('2010-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2010-01-01 10:05:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2010-01-01 10:09:59', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2010-01-01 10:10:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2099-01-01 10:00:33', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
)
-- #end of test-data
SELECT
  d, TRUNC(d) + FLOOR((d-TRUNC(d))*24*6)/(24*6)
FROM test_data

Answer 5

另一种方法，

select my_date - mod( (my_date-trunc(my_date))*24*60, 10)/24/60
from (
  select sysdate my_date from dual
);

一种可能更快的替代方法，因为它删除了对 trunc 的调用。

select my_date - mod( (my_date-to_date('1970', 'yyyy'))*24*60, 10)/24/60
from (
  select sysdate my_date from dual
);

Answer 6

为了返回下一个上限 10 分钟间隔，我使用了以下查询。我希望它有用，因为我不能简单地执行

trunc(sysdate, 'mi') + mod(EXTRACT(min FROM cast(sysdate as timestamp)), 10) / (24 * 60)< /code>

我这样做了，它对我有用。

select 
case when mod(EXTRACT(minute FROM cast(sysdate as timestamp)),5) between 1 and 4
      then trunc(sysdate,'mi')+((5*TRUNC(EXTRACT(minute FROM cast(sysdate as timestamp))/5,
0)+5)-EXTRACT(minute FROM cast(sysdate as timestamp)))/1440
      else trunc(sysdate,'mi')
end
from dual

这是基于这篇帖子。

Answer 7

我认为要解决这个问题，有一种更简单、更快捷的方法可以舍入到下一个较低的 10 秒、1 分钟、10 分钟等间隔。尝试使用 SUBSTR() 将时间戳作为字符串进行操作，如下所示：

SELECT 
SUBSTR(datetime(d),1,18)||'0' AS Slot10sec, 
SUBSTR(datetime(d),1,17)||'00' AS Slot1min, 
SUBSTR(datetime(d),1,15)||'0:00' AS Slot10min, 
SUBSTR(datetime(d),1,14)||'00:00' AS Slot1h, 
MY_VALUE
FROM MY_TABLE;