Click here to Skip to main content
15,867,973 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have timespan values stored in SQL server like 165:56,258:65,258:50.

I need sum of those values like 683:51

What I have tried:

SQL
WITH CTE_SUM AS (
  SELECT
    SUM(
      LEFT([tot.time], 2) * 3600 + SUBSTRING([tot.time], 4, 2) * 60
    ) AS SUM_AS_SECONDS
  FROM
    [dbo].[production dt]
  WHERE
    [ordernumber] = '800000025277'
    AND setup = 'I'
    AND machine IN('HMC-01', 'HMC-02')
)
SELECT
  isnull(
    CAST(SUM_AS_SECONDS / 3600 AS VARCHAR) + ':' + CAST(SUM_AS_SECONDS % 3600 / 60 AS VARCHAR),
    '00:00'
  )
FROM
  CTE_SUM
Posted
Updated 9-Jan-23 22:44pm
v3

Storing time-spans as strings is always a bad idea. Ideally, you should be storing them as a numeric value - for example, the total number of minutes.

If you're going to use strings, then you need to use them properly. Your first value is 165:56, so its clear that taking the first two characters as the number of hours isn't going to work.

You need to find the index of the : character, and split the string on that:
SQL
WITH cteTimes As
(
    SELECT
        SUM(
            CAST(LEFT([tot.time], CHARINDEX(':', [tot.time]) - 1) As int) * 3600
            + CAST(SUBSTRING([tot.time], CHARINDEX(':', [tot.time]) + 1, LEN([tot.time])) As int) * 60
        ) As TotalTimeInSeconds
    FROM
        dbo.[production dt]
    WHERE
        ordernumber = '800000025277'
    AND
        setup = 'I'
    AND
        maching In ('HMC-01', 'HMC-02')
)
SELECT
    CAST(TotalTimeInSeconds / 3600 As varchar(20)) + ':' + CAST((TotalTimeInSeconds % 3600) / 60 As varchar(20))
FROM
    cteTimes
;
NB: You have given your table and columns names which include "special" characters - "tot.time", "production dt" - which forces you to wrap them in square brackets in your queries. You should avoid this, and only use standard characters (A-Z, a-z, 0-9, _) in the names.
 
Share this answer
 
The most obvious solution is simple: don't store numeric values as strings - convert them to a "sensible" numeric value. For example, if the difference is usually measured in minutes and seconds, convert the timespans to seconds and store that in a INT field.
That way, you can do math on them directly without any fannying about with string chopping and conversion which gets incredibly slow on a large DB since SQL string handling is poor at best. And it means that the values stored are always valid instead of potentially corrupt.

I have no idea what intervals you samples are: they aren't minutes and seconds, or hours and minutes, they aren't formatted right for a "standard number" - so it could be anything, including invalid values already in your DB!
 
Share this answer
 
Comments
CPallini 10-Jan-23 4:44am    
5.
Richard Deeming 10-Jan-23 4:47am    
Treating it as hours and minutes gives the desired total, even though 258:65 should really be 259:05. :)
OriginalGriff 10-Jan-23 4:58am    
That's what I mean - already his DB has crap in it! :laugh:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900