SQL server, Converting Days,Hours,minutes to seconds

Question

I have a database column containing an NVARCHAR value that represents a systems up time in Days,hours,minutes and seconds(e.g- 1d 21h 59m 48s). I'd really like to Write a query to be able to show me that up time in a easy to read format seconds but I'm not quite sure how to do it.

Response_Time_AVG??????     Need to convert into seconds???
28m?    ???                1680(Manually done)???
2h 8m??                    7680(Manually done)
15m 4s? ??????? 
11h 14m???????? ??????? 
1h 9m 6s??????? ??????? 
15h 7m 6s?????? ??????? 
10h 12m 2s????? ??????? 
10h 19m 40s???? ??????? 
1d 5h 54m 6s??? ??????? 
1d 1h 23m 48s?? ??????? 
1d 21h 59m 48s? ??????? 

Answer 1

As I don't know what version of SQL Server you're on, and I don't have access to SQL Server 2016, or later, right now, I've use Jeff Moden's DelimitedSplit8K here. If you have a later version, you can use STRING_SPLIT, or any other String Splitter you like.

Anyway, firstly I split out the sections of the time at the white space. Then I JOIN on a set of Values which have the appropriate second values for a time period (seconds, minutes, hours and days). The WHERE simply filters in the event of a double space (' '), as an error would occur.

Then, in the SELECT, it's as simple as multipling the value by the seconds it represents:

WITH TimeValues AS(
    SELECT RTRIM(V.TS) AS TS
    FROM (VALUES ('28m           '),
                 ('2h 8m         '),
                 ('15m 4s        '),  
                 ('11h 14m       '),          
                 ('1h 9m 6s      '),          
                 ('15h 7m 6s     '),          
                 ('10h 12m 2s    '),          
                 ('10h 19m 40s   '),          
                 ('1d 5h 54m 6s  '),          
                 ('1d 1h 23m 48s '),          
                 ('1d 21h 59m 48s')) V(TS))
SELECT TV.TS,
       SUM(LEFT(DS.Item, LEN(DS.Item) - 1) * SV.Seconds) AS TotalSeconds
FROM TimeValues TV
     CROSS APPLY dbo.DelimitedSplit8K(TV.TS,' ') DS
     JOIN (VALUES ('s',1),('m',60),('h',3600),('d',86400)) SV(TimePeriod, Seconds) ON SV.TimePeriod = RIGHT(DS.Item,1)
GROUP BY TV.TS
ORDER BY TotalSeconds;

Output:

TS             TotalSeconds
-------------- ------------
15m 4s         904
28m            1680
1h 9m 6s       4146
2h 8m          7680
10h 12m 2s     36722
10h 19m 40s    37180
11h 14m        40440
15h 7m 6s      54426
1d 1h 23m 48s  91428
1d 5h 54m 6s   107646
1d 21h 59m 48s 165588

Note; I have used varchar here, not nvarchar. If you use this splitter, ensure you use the nvarchar version.