问题描述
我在SQL Server中有一个问题,需要四舍五入 datetime
.我在 rec_datetime
列中得到了 datetime
,但是我想在新列 r_datetime
中将这个 datetime
取整.对于整列 rec_datetime
,应四舍五入到最接近的15分钟.
I have a problem in SQL Server with rounding datetime
. I got datetime
in column rec_datetime
, but I want to round this datetime
in a new column r_datetime
, which has to be rounded to nearest 15 min, for the whole column rec_datetime
.
示例:
-
[2015-11-24 19:06:00.000]
-预期结果->[2015-11-24 19:00:00.000]
-
[2015-11-24 19:09:00.000]
-预期结果->[2015-11-24 19:15:00.000]
[2015-11-24 19:06:00.000]
- expected result ->[2015-11-24 19:00:00.000]
[2015-11-24 19:09:00.000]
- expected result ->[2015-11-24 19:15:00.000]
是否可以通过选择对整列进行舍入?诸如此类:
Is it possible to round it via select for whole column? Something like :
select round(rec_datetime.......
推荐答案
舍入,最近舍入&向上舍入到最接近的15分钟
DATEADD( minute, ( DATEDIFF( minute, 0, dateTimeX ) / 15 ) * 15, 0 ) AS dateTimeRoundDown
DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 15 * 60 ) / 2, dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundNearest
DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute, 15 , dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundUp
向下滚动
DATEADD( minute, ( DATEDIFF( minute, 0, dateTimeX ) / 15 ) * 15, 0 ) AS dateTimeRoundDown
获取以分钟为单位的偏移量(自基准日期以来的分钟数):
Get the offset in minutes (number of minutes since the base-date):
DATEDIFF( minute, 0, dateTimeX )
以整数除法向下舍入到15分钟为一个块
Round-down to 15 minute block by integer dividing:
DATEDIFF( minute, 0, dateTimeX ) / 15 ) * 15
在数分钟内重新添加基准日期:
Add the base-date back in minutes:
DATEADD( minute, ( DATEDIFF( minute, 0, dateTimeX ) / 15 ) * 15, 0 )
最接近的
DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 15 * 60 ) / 2, dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundNear
15/2分钟被添加到偏移量中.
15 / 2 minutes is added to the offset.
由于整数除法,需要以秒为单位.
Needs to be in seconds due to the integer division.
向上滚动
DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute, 15, dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundUp
15分钟被添加到偏移量
15 minutes is added to the offset
基准日期
我通常使用基准日期0(即SQL Server的纪元")
I generally use a base date of 0 which is the SQL Server 'epoch'
SELECT DATEADD( minute, 0, 0 ) -- '1900-01-01 00:00:00.000'
因为DATEADD()&DATEDIFF()使用INT(32位)的SQL Server数据类型作为参数,在不久的将来,这可能会导致溢出.
Because DATEADD() & DATEDIFF() use the SQL Server data type of INT (32 bits) for the parameters, for dates in the very far future, this may cause an overflow.
使用另一个固定日期,例如"2010-01-01",可以避免溢出.
Using another fixed date, eg '2010-01-01', will avoid the overflow.
所选基准日期的时间部分必须为00:00:00
The chosen base-date must have a time part of 00:00:00
使用基准日期和整数除法,无需强制转换&不需要浮点运算.
Using a base-date and integer division, no casting & no floating point operations are required.
单元测试
DECLARE @start DATETIME = '2017-04-20 21:00:00'
DECLARE @end DATETIME = '2017-04-20 22:00:00'
;WITH CTE_dateTimes AS
(
SELECT @start AS dateTimeX
UNION ALL
SELECT DATEADD( minute, 1, dateTimeX )
FROM CTE_dateTimes
WHERE DATEADD( minute, 1, dateTimeX ) <= @end
)
SELECT dateTimeX,
DATEADD( minute, ( DATEDIFF( minute, 0, dateTimeX ) / 15 ) * 15, 0 ) AS dateTimeRoundDown,
DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 15 * 60 ) / 2, dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundNearest,
DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute, 15 , dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundUp
FROM CTE_dateTimes
这篇关于在SQL Server中将日期时间舍入到最接近的15分钟的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!