sql server 2008 r2中的时间总和 [英] sum of time in sql server 2008 r2

问题描述

大家好，



i在sql中总和（时间）有问题。



其实我得到了价值观，但有一些小错误或者我被误解了。



i试过这个

Hi guys,

i have problem in doing sum(time) in sql.

actually i getting the values, but there is some minor bug or i'm missunderstood.

i have tried this

DECLARE @tb TABLE (Opt time);
 
INSERT INTO @tb (Opt)
VALUES('23:59'), ('23:59');
 
SELECT
    Sum(DateDiff(minute, 0, Opt)) As [TotalMinutes],
    
    Convert(varchar(5), Sum(DateDiff(minute, 0, Opt)) / 60)
    + ' hours '
    + Convert(char(2), Sum(DateDiff(minute, 0, Opt)) % 60)
    + ' minutes'
    As [Description]
FROM
    @tb
;

Result:  47:58

where as i'm looking for 48:00

but if i enter '24:00' instead of '23:59', its throwing an error.

what value should i enter to get 48:00

任何人都可以帮助我。



谢谢

Can any one please help me.

Thanks

推荐答案

试试这些..

try these..

SELECT
    Sum(DateDiff(minute, 0, Opt)) As [TotalMinutes],

    Convert(varchar(5), Sum(DateDiff(minute, 0, Opt)+1) / 60)
    + ' hours '
    + Convert(char(2), Sum(DateDiff(minute, 0, Opt)+1) % 60)
    + ' minutes'
    As [Description]
FROM
    time_table

更新......这是个坏主意，你最好看@Richard Deeming解决方案

Updated..It's bad idea,you better see @Richard Deeming solution

SELECT
    Sum(DateDiff(minute, 0, Opt)) As [TotalMinutes],
   case when Opt='23:59' then 
    Convert(varchar(5), Sum(DateDiff(minute, 0, Opt)+1) / 60)
    + ' hours '
    + Convert(char(2), Sum(DateDiff(minute, 0, Opt)+1) % 60)
    + ' minutes'
	else
	   Convert(varchar(5), Sum(DateDiff(minute, 0, Opt)) / 60)
    + ' hours '
    + Convert(char(2), Sum(DateDiff(minute, 0, Opt)) % 60)
    + ' minutes'
	end
    As [Description]

FROM
    time_table group by Opt

你得到的结果是正确的：

• 23小时59分钟是 1439 分钟;
• 两批 1439 分钟是 2878 分钟;
• 2878 分钟是 47小时58分钟;

The result you're getting is correct:

• 23 hours 59 minutes is 1439 minutes;
• Two lots of 1439 minutes is 2878 minutes;
• 2878 minutes is 47 hours 58 minutes;

time（Transact-SQL） [ ^ ]

让e： 00：00：00.0000000到23：59：59.9999999

Range: 00:00:00.0000000 through 23:59:59.9999999

这是有道理的，因为时间表示一天中的某个时间，并且没有 24点。 :)



如果您希望存储持续时间而不是特定时间，那么您将需要不同的数据类型。您可以使用 int 列并以分钟为单位存储持续时间，也可以存储两个 datetime2 列来表示事件的开始和结束时间，然后计算差异。

This makes sense, since time represents a time of day, and there isn't a 24 o'clock. :)

If you're looking to store a duration, rather than a specific time, then you'll need a different data type. You could either use an int column and store the duration in minutes, or you could store two datetime2 columns to represent the start and end times of the event, and then calculate the difference.

这篇关于sql server 2008 r2中的时间总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！