Tuesday, December 31, 2013

SQL Function / Query to take the number of seconds and convert it to a human readable duration

I little MSSQL function I made to give me a table with calculated human readable durations from seconds.

WolframAlpha link to verify durations

 1 use DB
 2 
 3 declare
 4    @s int
 5    ,@secs int
 6    ,@days int
 7    ,@hours int
 8    ,@minutes int
 9    ,@total_hours float
10    ,@total_minutes float
11    ,@txt_long varchar(200)
12    ,@txt_short varchar(200)
13    ,@abbr varchar(200)
14 
15 --- input seconds
16 select @s = 100000
17 
18 select @days    = @s / 86400
19 select @hours   = (@s % 86400) / 3600
20 select @minutes = ((@s % 86400) % 3600) / 60
21 select @secs    = (((@s % 86400) % 3600) % 60) % 60
22 select @total_hours   = CAST(@s as float)  / 3600
23 select @total_minutes = CAST(@s as float)  / 60
24 
25 
26 select @txt_long =
27 CASE WHEN @days > 1 THEN CAST(@days as varchar(20)) + ' days ' ELSE '' END +
28 CASE WHEN @days = 1 THEN CAST(@days as varchar(20)) + ' day ' ELSE '' END +
29 CASE WHEN @hours > 1 THEN CAST(@hours as varchar(20)) + ' hours ' ELSE '' END +
30 CASE WHEN @hours = 1 THEN CAST(@hours as varchar(20)) + ' hour ' ELSE '' END +
31 CASE WHEN @minutes > 1 THEN CAST(@minutes as varchar(20)) + ' minutes ' ELSE '' END +
32 CASE WHEN @minutes = 1 THEN CAST(@minutes as varchar(20)) + ' minute ' ELSE '' END +
33 CASE WHEN @secs > 1 THEN CAST(@secs as varchar(20)) + ' seconds ' ELSE '' END  +
34 CASE WHEN @secs = 1 THEN CAST(@secs as varchar(20)) + ' second ' ELSE '' END 
35 
36 select @txt_short =
37    CAST(@days as varchar(20)) + 'd ' +
38    CAST(@hours as varchar(20)) + 'h ' +
39    CAST(@minutes as varchar(20)) + 'm ' +
40    CAST(@secs as varchar(20)) + 's ' 
41 
42 select @abbr =
43    CAST(@days as varchar(20)) + ':' +
44    CAST(@hours as varchar(20)) + ':' +
45    CAST(@minutes as varchar(20)) + ':' +
46    CAST(@secs as varchar(20)) 
47 
48 select @days,@hours,@minutes,@secs,@total_hours,@total_minutes,@txt_long,@txt_short,@abbr
49 
50 RESULT:
51 "computed","computed1","computed2","computed3","computed4","computed5","computed6","computed7","computed8",
52 "1","3","46","40","27.7777777777777786","1666.66666666666674","1 day 3 hours 46 minutes 40 seconds ","1d 3h 46m 40s ","1:3:46:40",


You can select, copy and paste the code below




No comments:

Post a Comment