T-SQL：四舍五入到最接近的 15 分钟间隔
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/830792/
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
T-SQL: Round to nearest 15 minute interval
提问by Dzejms
What's the best way to round an HH:MM value to the closest 15 minute interval? I don't track seconds so they don't matter.
将 HH:MM 值四舍五入到最接近的 15 分钟间隔的最佳方法是什么？我不跟踪秒，所以它们无关紧要。
00:08:00 becomes 00:15:00
00:07:00 becomes 00:00:00
01:59:00 becomes 02:00:00
and so on. Is there an elegant, non UDF or Case statement method for doing this?
等等。是否有一种优雅的非 UDF 或 Case 语句方法来执行此操作？
EDIT:Here's the SQL I'm using to get the above values that I'd like to round:
编辑：这是我用来获取我想要舍入的上述值的 SQL：
CONVERT(CHAR(8), DATEADD(n, SUM(DATEDIFF(n, starttime, stoptime)), 0), 108)
starttimeand stoptimeare SQL datetimes.
starttime并且stoptime是 SQL datetime。
采纳答案by u07ch
This was answered here How to Round a Time in T-SQLand i think it should work for you to.
这是在此处如何在 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)
回答by hbrowser
I am currently using a dateadd / datediff variant with a zero (0) date for this. No Casting required:
我目前正在为此使用带有零 (0) 日期的 dateadd / datediff 变体。无需铸造：
select dateadd(minute, datediff(minute,0,GETDATE()) / 15 * 15, 0)
GETDATE() is whatever your datetime is.
GETDATE() 是您的日期时间。
This will work for dates at least up to the year 5500 before the datediff failes because of an overflow. However if you try to use second accuracy, above will fail right away.
在 datediff 由于溢出而失败之前，这至少适用于 5500 年之前的日期。但是，如果您尝试使用第二精度，则上述方法会立即失败。
Using another fixed date, like '2009-01-01', or Today's date (warning, more ugly SQL) will fix that. A future date will also work. As long as it has a time part of 00:00:00 you can base another datetime on it.
使用另一个固定日期，如“2009-01-01”或今天的日期（警告，更难看的 SQL）将解决这个问题。未来的日期也将起作用。只要它的时间部分为 00:00:00，您就可以在其上建立另一个日期时间。
for example: round to the nearest 30 seconds:
例如：四舍五入到最接近的 30 秒：
select dateadd(second, round(datediff(second, '2010-01-01', GETDATE()) / 30.0, 0) * 30, '2010-01-01');
回答by Jamie Paolino
I know this is a old post but wanted to share my answer. This builds on @hbrowser response. Here is what I've come up with. This will round up or down to the nearest 15 minutes.
我知道这是一个旧帖子，但想分享我的答案。这建立在@hbrowser 响应之上。这是我想出的。这将向上或向下舍入到最接近的 15 分钟。
SELECT DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, GETDATE()) / 15.0, 0) * 15, 0);
By doing this logic inline, rather than inside a user defined function, over large recordsets you should experience greater performance.
You can change the way rounding occurs by swapping the
ROUNDfunction to useFLOORorCAST expr AS INTto always round down or useCEILINGto always round up.
通过内联执行此逻辑，而不是在用户定义的函数内部，在大型记录集上您应该体验到更高的性能。
您可以通过交换
ROUND要使用的函数FLOOR或CAST expr AS INT始终向下舍入或使用CEILING始终向上舍入来更改舍入的方式。
Your individual use case will determine what style of rounding you may need to use.
您的个人用例将决定您可能需要使用的舍入方式。
The following script can be used to observe the differences offered by the different rounding techniques:
以下脚本可用于观察不同舍入技术提供的差异：
NOTE: to simplify the output each result has been casted to TIME(0), this is only done to simplify the output for this particular example.
注意：为了简化输出，每个结果都被转换为 TIME(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
回答by Andomar
You can round a date to the nearest quarter like:
您可以将日期四舍五入到最近的四分之一，例如：
cast(floor(cast(getdate() as float(53))*24*4)/(24*4) as datetime)
Casting datetime to double precesion to avoid overflows, double = float(53). Multiply by 24*4, the number of quarters in a day. Round to the nearest multiple of quarters with floor(), and then divide by 24*4 to convert back to normal time.
将日期时间转换为双倍进动以避免溢出，double = float(53)。乘以 24*4，即一天中的季度数。使用 floor() 舍入到最接近的季度倍数，然后除以 24*4 以转换回正常时间。
回答by Andomar
Tried Andomar's answer and there was rounding issues at 30 and 00 - so a few tweaks and this works perfectly:
尝试了 Andomar 的答案，并且在 30 和 00 处存在舍入问题 - 因此进行了一些调整，这非常有效：
cast(round(floor(cast(getdate() as float(53))*24*4)/(24*4),5) as smalldatetime)
This will show the last 15 minute increment, not the nearest, i.e. it won't go forward which is exactly what I needed.
这将显示最后 15 分钟的增量，而不是最近的增量，即它不会前进，这正是我所需要的。
回答by Surendra Dhanpaul
Okay easiest way:
好的最简单的方法：
convert the minutes to a decimal number by dividing by 60.
通过除以 60 将分钟转换为十进制数。
8/60 = 0.1333333333333333
multiply by 4
乘以 4
0.1333333333333333 * 4 = 0.5333333333333333
Round the product:
圆形产品：
Round(0.5333333333333333,0) = 1
divide the round number by 4
将整数除以 4
1/4 = 0.25 = 15 minutes
if you want the minutes just multiply it by 60
如果你想要分钟，只需乘以 60
0.25*60 = 15
Give a man a fish....
给一个人一条鱼......
回答by Rhift
--This is my favorite way to round time
--这是我最喜欢的打发时间的方式
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)
回答by MichaelJ
DECLARE @t time ='00:51:00.000'
DECLARE @m int = DATEPART(MI,@t)%15
-- 2008
SELECT DATEADD(mi,CASE WHEN @m >=8 THEN 15-@m ELSE -1*@m END,@t)
-- 2012
SELECT DATEADD(mi,IIF(@m >=8,15-@m,-1*@m),@t)
回答by Charles Bretana
Try this:
尝试这个：
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))
回答by Damián Herrera
To set block in 15 minutes:
要在 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
