sql server语言 - sql -如何在DateTime数据类型中忽略秒并舍入分钟

Question

在工作中，我们做了一个项目，要求一个团队在5天的特定时间段内每天清点学生8次。它们是:-

09:00, 10:00, 11:00, 13:15, 14:15, 14:50, 15:50, 16:20.

现在，收集的数据通过web应用程序直接放入数据库。问题是数据库使用标准的YYYY-MM-DD HH:MM:SS记录每条记录。MIL，但如果我按日期排序，然后按学生人数排序，会导致以下问题;例如:-

如果一个房间的学生在09:00:12数到5，但另一个房间在09:02:20数到0，我做了以下操作:

select student_count, audit_date
from table_name
order by audit_date, student_count;

查询将返回:

5 09:00:12
0 09:02:20

但是我想:

0 09:00:00
5 09:00:00

，因为我们要查找09:00期间每个房间的学生人数，但不幸的是，要收集数据，我们需要在那个小时内这样做，显然数据库会注意到这种准确性。此外，当涉及到14:15和14:50这两个时间段时，这个问题就变得更成问题了，因为我们需要能够区分这两个时间段。

是否有一种方法可以忽略DateTime的秒部分，并将分钟舍入到最接近的十分钟?

我使用SQL Server管理工作室2012。如果这一切都没有意义，我很抱歉!

Answer 1

您可能需要某种类型的周期表来存储您的段。然后，您可以使用它连接到计数表。

CREATE TABLE [Periods]
( -- maybe [id] INT,
    [start_time] TIME,
    [end_time] TIME
);
INSERT INTO [Periods]
VALUES  ('09:00','10:00'),
        ('10:00','11:00'),
        ('11:00','13:15'),
        ('13:15','14:15'),
        ('14:15','14:50'),
        ('14:50','15:50'),
        ('15:50','16:20'),
        ('16:20','17:00')
SELECT
    student_count, [start_time]
FROM table_name A
INNER JOIN [Periods] B
ON      CAST(A.[audit_date] AS TIME) >= B.[start_time]
    AND CAST(A.[audit_date] AS TIME) < B.[end_time]

Answer 2

您可以使用DATEADD和DATEPART函数与CASE表达式一起完成此操作。如果您想要在.14和.50周期之间更精确地截止，您可以轻松地调整case语句，并且如果您希望分钟为.10或.15

-- some test data
declare @t table (the_time time)
insert @t values ('09:00:12')
insert @t values ('14:16:12')
insert @t values ('09:02:12')
insert @t values ('14:22:12')
insert @t values ('15:49:12')
insert @t values ('15:50:08')
select 
    the_time, 
    case 
        when datepart(minute,the_time) < 15 then 
            dateadd(second, -datepart(second,the_time),dateadd(minute, -datepart(minute,the_time),the_time)) 
        when datepart(minute,the_time) >= 15 and datepart(minute,the_time) < 50 then 
            dateadd(second, -datepart(second,the_time),dateadd(minute, -datepart(minute,the_time)+10,the_time)) 
        else
            dateadd(second, -datepart(second,the_time),dateadd(minute, -datepart(minute,the_time)+50,the_time)) 
    end as WithoutSeconds
from @t

结果:

the_time         WithoutSeconds
---------------- ----------------
09:00:12.0000000 09:00:00.0000000
14:16:12.0000000 14:10:00.0000000
09:02:12.0000000 09:00:00.0000000
14:22:12.0000000 14:10:00.0000000
15:49:12.0000000 15:10:00.0000000
15:50:08.0000000 15:50:00.0000000

Answer 3

试试这个:

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

Answer 4

你可以试试ORDER BY this formula

DATEADD(minute, floor((DATEDIFF(minute, '20000101', audit_date) + 5)/10)*10, '20000101')

。

WITH tbl AS(
  SELECT * FROM ( VALUES (5,'2014-03-28 09:00:09.793'),(0,'2014-03-28 09:02:20.123')) a (student_count, audit_date)
)
SELECT  *,DATEADD(minute, floor((DATEDIFF(minute, '20000101', audit_date) + 5)/10)*10, '20000101') as ORDER_DT
FROM    tbl
ORDER BY ORDER_DT,student_count

SQL小提琴