SQL 将日期舍入到 10 分钟间隔
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2192424/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Round date to 10 minutes interval
提问by Peter Lang
I have a DATEcolumn that I want to round to the next-lower 10 minute interval in a query (see example below).
我有一个DATE列，我想在查询中舍入到下一个较低的 10 分钟间隔（请参见下面的示例）。
I managed to do it by truncating the seconds and then subtracting the last digit of minutes.
我设法通过截断秒数然后减去分钟的最后一位数字来做到这一点。
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
And here is the result:
结果如下：
01.01.2010 10:00:00 01.01.2010 10:00:00
01.01.2010 10:05:00 01.01.2010 10:00:00
01.01.2010 10:09:59 01.01.2010 10:00:00
01.01.2010 10:10:00 01.01.2010 10:10:00
01.01.2099 10:00:33 01.01.2099 10:00:00
01.01.2010 10：00:00 01.01.2010 10：00:00
01.01.2010 10：05:00 01.01.2010 10：00:00
01.01.2010 10：9时59 01.01.2010 10：00:00
01.01。 2010 年 10: 10:00 01.01.2010 10: 10:00
01.01.2099 10: 00:33 01.01.2099 10: 00:00
Works as expected, but is there a better way?
按预期工作，但有更好的方法吗？
EDIT:
编辑：
I was curious about performance, so I did the following test with 500.000 rows and (not really) random dates. I am going to add the results as comments to the provided solutions.
我对性能很好奇，所以我用 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;
This approach took 03.24 s.
这种做法采取了03.24 s。
采纳答案by Tom
select
trunc(sysdate, 'mi')
- numtodsinterval(mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10), 'minute')
from dual;
or even
甚至
select
trunc(sysdate, 'mi')
- mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10) / (24 * 60)
from dual;
回答by Jim Hudson
I generally hate doing date -> character -> date conversions when it's not necessary. I'd rather use numbers.
我通常讨厌在不需要时进行日期 -> 字符 -> 日期转换。我宁愿使用数字。
select trunc((sysdate - trunc(sysdate))*60*24,-1)/(60*24)+trunc(sysdate) from dual;
This extracts the minutes from the current day, truncates them down to the 10-minute interval, and then adds them back in to make it a date again. Of course, you can replace sysdate with whatever date you want. It trusts implicit conversions a lot more than I want but at least it'll work for any NLS date format.
这将从当天提取分钟，将它们截断为 10 分钟间隔，然后将它们添加回以使其再次成为日期。当然，您可以将 sysdate 替换为您想要的任何日期。它比我想要的更信任隐式转换，但至少它适用于任何 NLS 日期格式。
回答by a'r
Another method,
另一种方法，
select my_date - mod( (my_date-trunc(my_date))*24*60, 10)/24/60
from (
select sysdate my_date from dual
);
An alternative that might be quicker as it removes the call to trunc.
一种可能更快的替代方法，因为它删除了对 trunc 的调用。
select my_date - mod( (my_date-to_date('1970', 'yyyy'))*24*60, 10)/24/60
from (
select sysdate my_date from dual
);
回答by Nick Larsen
You could take the returned value as a string and substring the left side up to the last minute digit and replace it with a 0. I wouldn't exactly say thats better unless you provide some kind of metric.
您可以将返回值作为字符串并将左侧子字符串化到最后一分钟数字并将其替换为0. 除非您提供某种指标，否则我不会说那更好。
回答by Tony Andrews
Not necessarily any better, but another method:
不一定更好，而是另一种方法：
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
回答by Mahesh
To return the next upper 10 minute interval, I used the following query. I hope it'll be useful because I couldn't simply do a
为了返回下一个较高的 10 分钟间隔，我使用了以下查询。我希望它会有用，因为我不能简单地做一个
trunc(sysdate, 'mi') + mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10) / (24 * 60)
trunc(sysdate, 'mi') + mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10) / (24 * 60)
I did this and it worked for me.
我这样做了，它对我有用。
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 dualThis is based on thispost.
这是基于这篇文章。
回答by McPeppr
I think to solve this there's a much easier and faster way to round to a next lower 10 seconds, 1 Minute, 10 Minute etc. interval. Try to manipulate your timestamp as a string using SUBSTR() like this:
我认为要解决这个问题，有一种更简单快捷的方法可以舍入到下一个较低的 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;
