Подтвердить что ты не робот

T-SQL: от ближайшего 15-минутного интервала

Какой лучший способ округлить значение HH: MM до ближайшего 15-минутного интервала? Я не отслеживаю секунды, поэтому они не имеют значения.

00:08:00 becomes 00:15:00 
00:07:00 becomes 00:00:00 
01:59:00 becomes 02:00:00

и т.д. Есть ли элегантный, не UDF или Case выражение метод для этого?

EDIT: Здесь SQL, который я использую для получения вышеуказанных значений, которые я хотел бы округлить:

CONVERT(CHAR(8), DATEADD(n, SUM(DATEDIFF(n, starttime, stoptime)), 0), 108)

starttime и stoptime являются SQL datetime s.

4b9b3361

ОТВЕТЫ

Ответ 1

Здесь был дан ответ Как сделать раунд времени в T-SQL, и я думаю, что он должен работать для вас.

CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float) RETURNS datetime
AS
BEGIN
    DECLARE @RoundedTime smalldatetime, @Multiplier float

    SET @Multiplier = 24.0 / @RoundTo

    SET @RoundedTime= ROUND(CAST(CAST(CONVERT(varchar, @Time, 121) AS datetime) AS float) * @Multiplier, 0) / @Multiplier

    RETURN @RoundedTime
END

-- Usage    
SELECT dbo.RoundTime('13:15', 0.5)

Ответ 2

В настоящее время я использую dateadd/dateiff вариант с нулевой (0) датой для этого. Нет Требуется литье:

select dateadd(minute, datediff(minute,0,GETDATE()) / 15 * 15, 0)

GETDATE() - это то, что было в вашем datetime.

Это будет работать в течение дат, по крайней мере, до 5500 года до того, как датфиксу не удастся из-за переполнения. Однако, если вы попытаетесь использовать вторую точность, выше будет сбой сразу.

Используя другую фиксированную дату, например "2009-01-01" или "Сегодня" (предупреждение, более уродливый SQL), это исправит. Будет также работать будущая дата. До тех пор, пока у него есть временная часть 00:00:00, вы можете создать другое время на нем.

например: округление до ближайших 30 секунд:

select dateadd(second, round(datediff(second, '2010-01-01', GETDATE()) / 30.0, 0) * 30, '2010-01-01');

Ответ 3

Я знаю, что это старый пост, но хотел бы поделиться своим ответом. Это основано на ответе @hbrowser. Вот что я придумала. Это округляется в большую или меньшую сторону до ближайших 15 минут.

SELECT DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, GETDATE()) / 15.0, 0) * 15, 0);

Делая эту логику встроенной, а не внутри пользовательской функции, над большими наборами записей вы должны добиться большей производительности.

Вы можете изменить способ округления, переключив функцию ROUND, чтобы использовать FLOOR или CAST expr AS INT, чтобы всегда округлять, или использовать CEILING, чтобы всегда округлять вверх.

Ваш индивидуальный вариант использования определит, какой стиль округления вам может понадобиться.

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

ПРИМЕЧАНИЕ. Чтобы упростить вывод, каждый результат был приведен к ВРЕМЕНИ (0), это делается только для упрощения вывода для этого конкретного примера.

DECLARE @SequenceStart SmallDateTime = CAST(GETDATE() AS Date); 
DECLARE @SequenceEnd SmallDateTime = DateAdd(HOUR, 2, @SequenceStart); -- Recursive CTEs should always have an upper limit
DECLARE @SequenceIntMins INT = 5; -- increment by 5 to show the difference with rounding
WITH TimeSequence([Time]) as
(
    SELECT @SequenceStart as [Time]
    UNION ALL
    SELECT DateAdd(MINUTE, 5, [Time]) FROM TimeSequence 
    WHERE [Time] <= @SequenceEnd
)
    SELECT [Time] = Cast([Time] as TIME(0))
    , Rounded = CAST(DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, [Time]) / 15.0, 0) * 15, 0) as TIME(0))
    , Casted = CAST(DATEADD(MINUTE, CAST(DATEDIFF(MINUTE, 0, [Time]) / 15.0 AS INT) * 15, 0) as TIME(0))
    , Floored = CAST(DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, [Time]) / 15.0) * 15, 0) as TIME(0))
    , Ceilinged = CAST(DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, [Time]) / 15.0) * 15, 0) as TIME(0))
FROM TimeSequence OPTION ( MaxRecursion 1000);
-- MaxRecursion may be neccessary if you change the interval or end of the sequence

Time        Rounded     Casted      Floored     Ceilinged
00:00:00    00:00:00    00:00:00    00:00:00    00:00:00
00:05:00    00:00:00    00:00:00    00:00:00    00:15:00
00:10:00    00:15:00    00:00:00    00:00:00    00:15:00
00:15:00    00:15:00    00:15:00    00:15:00    00:15:00
00:20:00    00:15:00    00:15:00    00:15:00    00:30:00
00:25:00    00:30:00    00:15:00    00:15:00    00:30:00
00:30:00    00:30:00    00:30:00    00:30:00    00:30:00
00:35:00    00:30:00    00:30:00    00:30:00    00:45:00
00:40:00    00:45:00    00:30:00    00:30:00    00:45:00
00:45:00    00:45:00    00:45:00    00:45:00    00:45:00
00:50:00    00:45:00    00:45:00    00:45:00    01:00:00
00:55:00    01:00:00    00:45:00    00:45:00    01:00:00
01:00:00    01:00:00    01:00:00    01:00:00    01:00:00
01:05:00    01:00:00    01:00:00    01:00:00    01:15:00

Ответ 4

Вы можете округлить дату до ближайшего квартала, например:

cast(floor(cast(getdate() as float(53))*24*4)/(24*4) as datetime)

Приведение datetime в двойное precesion, чтобы избежать переполнения, double = float (53). Умножьте на 24 * 4, количество кварталов за день. Раунд до ближайшего кратного четверти с полом(), а затем разделите на 24 * 4, чтобы вернуться в нормальное время.

Ответ 5

Пробовал Andomar ответить, и были проблемы округления в 30 и 00 - так несколько настроек, и это отлично работает:

cast(round(floor(cast(getdate() as float(53))*24*4)/(24*4),5) as smalldatetime)

Это покажет последнее 15-минутное приращение, а не ближайшее, то есть оно не будет идти вперед, что именно то, что мне нужно.

Ответ 6

Самый простой способ:

преобразуйте минуты в десятичное число, разделив на 60.

8/60 = 0.1333333333333333

умножить на 4

0.1333333333333333 * 4   = 0.5333333333333333

Ракурс продукта:

Round(0.5333333333333333,0) = 1

разделите круглое число на 4

1/4 = 0.25 = 15 minutes

если вы хотите, чтобы минуты просто умножали его на 60

0.25*60 = 15

Дайте человеку рыбу...

Ответ 7

Попробуйте следующее:

Declare @Dt DateTime 
Set @Dt = getDate()

Select DateAdd(minute, 
        15 * ((60 * Datepart(hour, @Dt) + 
        Datepart(Minute, @Dt)+ 
        Case When DatePart(second, @Dt) < 30 
        Then 7 Else 8 End) / 15),
    DateAdd(day, DateDiff(day, 0, @Dt), 0))

Ответ 8

DECLARE @t time  ='00:51:00.000' 
DECLARE @m  int = DATEPART(MI,@t)%15

-- 2008
SELECT DATEADD(mi,CASE WHEN @m >=8 THEN [email protected] ELSE -1*@m END,@t)

-- 2012
SELECT DATEADD(mi,IIF(@m >=8,[email protected],-1*@m),@t)

Ответ 9

- Это мой любимый способ округления времени

DECLARE @Time DATETIME = GETDATE()
       ,@RoundInterval INT = 30  --in minutes, needs to be a number that can be divided evenly into 60
       ,@RoundDirection INT = 2  --0 is down to the last interval, 1 is to the nearest interval, 2 is up to the next interval

SELECT  DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(SECOND,30*@RoundDirection*@RoundInterval,@Time))/@RoundInterval*@RoundInterval,0)

Ответ 10

create function RoundQuarterHour
(
    @dt datetime
)
returns datetime
as
begin
    declare @result datetime
    declare @mm int
    set @mm=datepart(minute,@dt)
    set @result = dateadd(minute,[email protected] + (round(@mm/cast(15 as float),0)*15) , @dt )

    return @result
end
go


           select dbo.RoundQuarterHour('2009-may-5 20:00') , '00'
 union all select dbo.RoundQuarterHour('2009-may-5 20:01') , '01'
 union all select dbo.RoundQuarterHour('2009-may-5 20:07') , '07'
 union all select dbo.RoundQuarterHour('2009-may-5 20:08') , '08'
 union all select dbo.RoundQuarterHour('2009-may-5 20:22') , '22'
 union all select dbo.RoundQuarterHour('2009-may-5 20:23') , '23'
 union all select dbo.RoundQuarterHour('2009-may-5 20:37') , '37'
 union all select dbo.RoundQuarterHour('2009-may-5 20:38') , '38'
 union all select dbo.RoundQuarterHour('2009-may-5 20:52') , '52'
 union all select dbo.RoundQuarterHour('2009-may-5 20:53') , '53'
 union all select dbo.RoundQuarterHour('2009-may-5 20:59') , '59'

Ответ 11

Время округления в T-SQL на самом деле очень проблематично и много раз неточно.

Несколько лет назад я перебирал все время в код и использовал все дополнительные хаб-бары, которые нужно делать в T-SQL, чтобы это произошло и произошло точно. Время округления в коде проще и точнее.

Если вы застряли в T-SQL и не имеете вспомогательного кода или не имеете доступа к этому коду, следуйте приведенным выше примерам. В противном случае я смиренно рекомендую, чтобы код выполнял работу.

Ответ 12

как насчет этого? (переменная добавлена ​​для удобочитаемости)

create function dbo.FloorTimeToQuarters
(
 @dt as datetime
)
RETURNS datetime
as

BEGIN

 DECLARE @timeAsInt bigint
 SET @timeAsInt = ( cast( @dt as float ) * 96 )
 RETURN DateAdd( hour, @timeAsInt % 96, cast( @timeAsInt / 96 as datetime)  )

END

Ответ 13

Чтобы установить блок за 15 минут:

CREATE FUNCTION RoundQuarterHour (
    @dt DATETIME
) RETURNS DATETIME

AS
BEGIN
    DECLARE @date DATETIME
    SET @date = CONVERT(varchar(16),@dt,121) --Sin segundos, ni milisegundos
    RETURN DATEADD(MINUTE,(DATEPART(MINUTE,@date) % 15)*-1, @date)
END

PRINT dbo.RoundQuarterHour('2011/01/01 18:00:07')  --Jan  1 2011  6:00PM
PRINT dbo.RoundQuarterHour('2011/01/01 18:01:07')  --Jan  1 2011  6:00PM
PRINT dbo.RoundQuarterHour('2011/01/01 18:13:07')  --Jan  1 2011  6:00PM
PRINT dbo.RoundQuarterHour('2011/01/01 18:14:07')  --Jan  1 2011  6:00PM
PRINT dbo.RoundQuarterHour('2011/01/01 18:15:07')  --Jan  1 2011  6:15PM
PRINT dbo.RoundQuarterHour('2011/01/01 18:16:07')  --Jan  1 2011  6:15PM

Ответ 14

Это будет округлено до ближайших 15 минут. Вы можете изменить @ROUND на выбранный вами интервал.

Declare @Dt DateTime = '2016-01-01 14:38:00' 
DECLARE @ROUND int = 15;
SELECT
CASE WHEN (DATEPART(MINUTE, @Dt) % @ROUND) * 60 + DATEPART(SECOND, @Dt) < (30 * @ROUND)
THEN DATEADD(minute, datediff(minute,0, @Dt) / @ROUND * @ROUND, 0) 
ELSE DATEADD(minute, (DATEDIFF(minute,0, @Dt) / @ROUND * @ROUND) + @ROUND, 0) 
END

Ответ 15

Помещение разбивается на выяснение того, какой прирост вы хотите, что это за процент 60 минут... затем вычислите необходимое количество приращений, чтобы добраться туда... возьмите значение INT (это отбивает остатки) и там у вас есть, простая функция для округления вверх или вниз до ближайшего приращения.

Простая функция:

    ALTER FUNCTION [dbo].[RoundOffDateTime]
(
    @IncDate    DATETIME,
    @Increment  INT
)
RETURNS SMALLDATETIME
AS
BEGIN

    DECLARE @IncrementPercent DECIMAL(2,2) = CAST(@Increment as decimal)/60
    DECLARE @IncMinutes REAL = ROUND(CAST(DATEPART(mi,@IncDate) as decimal)/CAST(@Increment as decimal),0)
    DECLARE @MinutesNeeded INT = CAST(@IncMinutes * @Increment as INT)

    RETURN CAST(DATEADD(mi,@MinutesNeeded,DATEADD(ss,-DATEPART(ss,@IncDate),DATEADD(mi,-DATEPART(mi,@IncDate),@IncDate))) as smalldatetime)

END

Ответ 16

    DECLARE   @Date             DATETIME = GETDATE()

    SELECT    @Date
            , DATEADD(ms, 900000 - DATEDIFF(ms, CAST(@Date AS DATE), @Date) % 900000, @Date)