在SQL Server中将日期时间舍入到最接近的15分钟 [英] Rounding Datetime to nearest 15 minutes in SQL Server

问题描述

我在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分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！