Получите десятиминутные интервалы из диапазона дат и времени

У меня есть столбец datetime с диапазоном значений Datetime. Я хочу создать еще один столбец со всеми этими значениями даты и времени, но округлить до десяти минут.

Итак, что-то вроде этого:

datetimesent              |     ten_minute_column

2012-06-11 18:27:58.000   |     2012-06-11 18:20:00.000
2012-06-15 15:19:08.000   |     2012-06-15 15:10:00.000
...                       |

Самое дальнее, что у меня есть, это играть в слоты для минутных ведер. Я получил это, выполнив:

SELECT DatetimeSent,
    DATEADD(Minute, DATEDIFF(Minute, 0, DatetimeSent), 0) AS Minute_bucket
FROM allrequests

Но мне нужно десятиминутное ведро.


sql sql-server sql-server-2008
person dublintech    schedule 25.06.2012    source источник
comment
Попробуйте: stackoverflow. com/questions/249794/how-to-round-a-time-in-t-sql. В этом посте также много примеров: stackoverflow.com/questions/830792/   -  person Void Ray    schedule 25.06.2012

Ответы (3)


arrow_upward
8
arrow_downward

Попробуй это:

select dateadd(minute, datepart(minute, datetimesent) / 10 * 10, 
dateadd(hour, datediff(hour, 0,datetimesent), 0)) ten_minute_column
from 
(select cast('2012-06-11 18:27:58.000' as datetime) datetimesent
union all
 select cast('2012-06-15 15:19:08.000' as datetime)) a
person t-clausen.dk    schedule 25.06.2012

arrow_upward
3
arrow_downward

Вы можете сделать это, используя множество функций:

WITH D AS
(   SELECT  CURRENT_TIMESTAMP [DateField]
    UNION ALL
    SELECT  DATEADD(MINUTE, 5, CURRENT_TIMESTAMP)
)
SELECT  DATEADD(MINUTE, (10 * FLOOR(DATEPART(MINUTE, DateField) / 10.0)) - DATEPART(MINUTE, DateField), DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DateField), 0)) AS RoundedDate
FROM    D

Суть этого заключается в том, чтобы вычесть количество минут после 10-минутного интервала и вычесть это из фактического количества минут (с удаленными секундами).

Это можно немного исправить, переместив некоторые функции в объединение. Тем не менее, я не думаю, что это дает какой-либо прирост производительности (вообще не тестировался)

;WITH T AS
(   SELECT  Number,
            (10 * FLOOR(Number / 10.0)) - Number [RoundedDifference]
    FROM    (   SELECT  ROW_NUMBER() OVER(ORDER BY Object_ID) - 1 [Number]
                FROM    sys.All_Objects
            ) n
    WHERE   Number < 60
), D AS
(   SELECT  CURRENT_TIMESTAMP [DateField]
    UNION ALL
    SELECT  DATEADD(MINUTE, 5, CURRENT_TIMESTAMP)
)
SELECT  DateField,
        DATEADD(MINUTE, RoundedDifference, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DateField), 0)) [RoundedDate]
FROM    D
        INNER JOIN T
            ON DATEPART(MINUTE, DateField) = Number
person GarethD    schedule 25.06.2012
comment
Он не выглядит таким чистым, как принятый ответ, но это ОПРЕДЕЛЕННО правильный ответ на этот вопрос! - person NTDLS; 07.02.2013

arrow_upward
0
arrow_downward

Предполагая, что никогда не бывает миллисекунд, вы можете разделить минуты и секунды таким образом и сгруппировать по результату:

SELECT DATEADD(SECOND, -(CONVERT(INT, RIGHT(CONVERT(CHAR(2),
  DATEPART(MINUTE, GETDATE())),1))*60)-(DATEPART(SECOND,GETDATE())), GETDATE());

Вот запрос, который получает правильное количество временных интервалов на основе минимальной и максимальной дат из таблицы (или подмножества таблицы):

DECLARE @x TABLE(datetimesent DATETIME);
INSERT @x SELECT '2012-06-11 18:27:58.000'
UNION ALL SELECT '2012-06-15 15:19:08.000';

DECLARE @start SMALLDATETIME, @end SMALLDATETIME, @i INT;

SELECT @start = CONVERT(DATE, MIN(datetimesent)), @end = CONVERT(DATE,
MAX(datetimesent))
FROM @x
-- WHERE ...;

SELECT @i = DATEDIFF(DAY, @start, @end) * 144;

;WITH slots(ten_minute_column) AS
(
   SELECT TOP (@i * 144) DATEADD(MINUTE, 10 * (ROW_NUMBER() OVER
   (ORDER BY s1.[object_id])-1), @start)
   FROM sys.all_columns AS s1
   -- you may need to cross join to another table if this doesn't
   -- provide enough rows. Depends on overall datediff...
)
SELECT x.datetimesent, slots.ten_minute_column
 FROM @x AS x
 INNER JOIN slots
 ON x.datetimesent >= slots.ten_minute_column
 AND x.datetimesent < DATEADD(MINUTE, 10, slots.ten_minute_column)
 -- WHERE ...;

Результаты:

datetimesent              ten_minute_column
-----------------------   -------------------
2012-06-11 18:27:58.000   2012-06-11 18:20:00
2012-06-15 15:19:08.000   2012-06-15 15:10:00
person Aaron Bertrand    schedule 25.06.2012