SQL server, Converting Seconds to Minutes, Hours, Days

Question

I have a database column containing an integer value that represents a systems up time in seconds. I'd really like a query to be able to show me that up time in a easy to read format day(s) hour(s) minute(s) but I'm not quite sure how to do it. A lot of examples I've found appear to use parameters as an example but never much of how to use it in a select function.

I need the time to be the same as what's displayed on a website too. I tried one query earlier and its added days and removed minutes. Can anyone help me out?

Source data:

PDT0014 6141
PDT0008 4990
PDT0024 840227
PDT0033 2301
PDT0035 5439
PDT0005 3434
PDT0019 5482

Sample code:

SELECT tblAssets.AssetName, 
(case when tblAssets.Uptime> (24*60*60) 
        then 
            cast(datepart(day,datediff(dd, 0, dateadd(second, tblAssets.Uptime, 0))) as varchar(4))
        +   ' Day(s) ' + convert(varchar(2), dateadd(second, tblAssets.Uptime, 0), 108) +' Hour(s)' 
    else
            convert(varchar(5), dateadd(second, tblAssets.Uptime, 0), 108) + ' Hour(s) Minute(s) ' 
    end) AS Uptime
FROM tblAssets

Desired Query Output:

PDT0014 01:42 Hour(s) Minute(s) 
PDT0008 01:23 Hour(s) Minute(s) 
PDT0024 10 Day(s) 17 Hour(s)
PDT0033 00:38 Hour(s) Minute(s) 
PDT0035 01:30 Hour(s) Minute(s) 
PDT0005 00:57 Hour(s) Minute(s) 
PDT0019 01:31 Hour(s) Minute(s)

show us what you have tried and the current output along with what you would like the output to be. Sample code and output will help you get better answers. — Tanner, Oct 10 '13 at 14:24
CONVERT(varchar, uptime / 86400) + " days " + CONVERT(varchar, (uptime % 86400) / 3600) + " hours " + ... — adrianm, Oct 10 '13 at 14:30
Wait, is `Output:` your *desired* output, or the output you're getting now (that isn't good enough)? — Aaron Bertrand, Oct 10 '13 at 14:42
Nah that's the output my query was giving, not the desired one! — Tinydan, Oct 10 '13 at 14:45
To be ultra clear (and since folks often ignore comments), you should add a code block that shows the actual output you want. — Aaron Bertrand, Oct 10 '13 at 14:55

Aaron Bertrand · Answer 1 · 2013-10-10T14:41:53.697

14

Depending on the output you want:

DECLARE @s INT = 139905;

SELECT                CONVERT(VARCHAR(12), @s /60/60/24) + ' Day(s), ' 
  +                   CONVERT(VARCHAR(12), @s /60/60 % 24) 
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2),  @s /60 % 60), 2) 
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2),  @s % 60), 2);

Result:

1 Day(s), 14:51:45

Or:

DECLARE @s INT = 139905;

SELECT 
    CONVERT(VARCHAR(12), @s /60/60/24)   + ' Day(s), ' 
  + CONVERT(VARCHAR(12), @s /60/60 % 24) + ' Hour(s), '
  + CONVERT(VARCHAR(2),  @s /60 % 60)    + ' Minute(s), ' 
  + CONVERT(VARCHAR(2),  @s % 60)        + ' Second(s).';

Result:

1 Day(s), 14 Hour(s), 51 Minute(s), 45 Second(s).

You can replace 60/60/24 with 86400 etc. but I find it better self-documenting if you leave in the /seconds/minutes/hours calculations. And if you are going against a table, just use column_name in place of @s.

edited Oct 10 '13 at 14:41

answered Oct 10 '13 at 14:30

Aaron Bertrand

272,866
37
466
490

Alright I'm liking the look of the second query, where would I read the column name into it though? – Tinydan Oct 10 '13 at 14:40
You would replace `@s` with the column name. – Aaron Bertrand Oct 10 '13 at 14:40
Well I'm a fool aren't I. I've been scratching my head at these parameters wondering how i could apply it to my situation! – Tinydan Oct 10 '13 at 14:46

GarethD · Accepted Answer · 2013-10-10T14:57:38.937

I tend to use:

CAST(FLOOR(seconds / 86400) AS VARCHAR(10))+'d ' +
    CONVERT(VARCHAR(5), DATEADD(SECOND, Seconds, '19000101'), 8)

The top part just gets your days as an integer, the bottom uses SQL-Server's convert to convert a date into a varchar in the format HH:mm:ss after converting seconds into a date.

e.g.

SELECT  Formatted = CAST(FLOOR(seconds / 86400) AS VARCHAR(10))+'d ' +
                        CONVERT(VARCHAR(5), DATEADD(SECOND, Seconds, '19000101'), 8),
        Seconds
FROM    (   SELECT  TOP 10 
                    Seconds = (ROW_NUMBER() OVER (ORDER BY Object_ID) * 40000)
            FROM    sys.all_Objects
            ORDER BY Object_ID
        ) S

Example on SQL Fiddle

N.B. Change CONVERT(VARCHAR(5), DATEADD(.. to CONVERT(VARCHAR(8), DATEADD(.. to keep the seconds in the result

EDIT

If you don't want seconds and need to round to the nearest minute rather than truncate you can use:

SELECT  Formatted = CAST(FLOOR(ROUND(Seconds / 60.0, 0) * 60 / 86400) AS VARCHAR(10))+'d ' +
                        CONVERT(VARCHAR(5), DATEADD(SECOND, ROUND(Seconds / 60.0, 0) * 60, '19000101'), 8),
        Seconds
FROM    (   SELECT  Seconds = 3899
        ) S

I have just replaced each reference to the column seconds with:

ROUND(Seconds / 60.0, 0) * 60

So before doing the conversion rounding your seconds value to the nearest minute

great query, does the fact it drops seconds affect rounding at all? — Tinydan, Oct 10 '13 at 14:44
Yes. It will truncate the duration, so 1 hour, 4 minutes and 59 seconds will show as `0d 01:04`, and won't round up (as it probably should to `0d 01:05`. — GarethD, Oct 10 '13 at 14:51

JDB · Answer 3 · 2013-10-10T14:55:34.100

You can convert seconds to days by dividing by 86400

You can convert seconds to hours by dividing by 3600, but you need to get the remainder (by subtracting off the total days converted to hours)

You can convert seconds to minutes by dividing by 60, but you need to get the remainder (by subtracting off the total hours converted to minutes)

Seconds you can just report, but like minutes you want to only report the remainder of seconds (by sutracting off the total minutes converted to seconds)

SELECT      FLOOR( UpTime / 86400 ) AS DAYS
        ,   FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 ) AS HOURS
        ,   FLOOR( ( UpTime / 60 ) - FLOOR( UpTime / 3600 ) * 60 ) AS MINUTES
        ,   UpTime - FLOOR( UpTime / 60 ) * 60 AS SECONDS
FROM        ( SELECT 269272 AS UpTime ) AS X

269272 represents 3 days (259200 seconds), 2 hours (7200 seconds), 47 minutes (2820 seconds) and 52 seconds.

This query produces:

| DAYS | HOURS | MINUTES | SECONDS |
------------------------------------
|    3 |     2 |      47 |      52 |

Substituting 125 (2 minutes, 5 seconds) for 259200 will produce:

| DAYS | HOURS | MINUTES | SECONDS |
------------------------------------
|    0 |     0 |       2 |       5 |

To convert this to a string representation, you can use SQL Server 2012's FORMAT function:

SELECT  CASE
            WHEN DAYS > 0 THEN
                FORMAT( DAYS, '##' ) + ' Day(s) ' + FORMAT( HOURS, '##' ) + ' Hour(s)'
            ELSE
                FORMAT( HOURS, '##' ) + ':' + FORMAT( MINUTES, '##' ) + ' Hour(s) Minute(s)'
        END AS UpTimeString
FROM (
    SELECT      FLOOR( UpTime / 86400 ) AS DAYS
            ,   FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 ) AS HOURS
            ,   FLOOR( ( UpTime / 60 ) - FLOOR( UpTime / 3600 ) * 60 ) AS MINUTES
            ,   UpTime - FLOOR( UpTime / 60 ) * 60 AS SECONDS
    FROM        ( SELECT 125 AS UpTime ) AS X
) AS UptimeSubselect

Thanks for the effort, Looks like a great bit of SQL. – Tinydan Oct 10 '13 at 15:35 — Tinydan, Oct 10 '13 at 15:35

score 2 · Answer 4 · answered Oct 10 '13 at 14:51

This is another approach using DATEPART():

DECLARE @S INT = 86472,
        @START DATETIME = CONVERT(DATETIME,0)
DECLARE @END DATETIME = DATEADD(SECOND,@S, @START)

SELECT CONVERT(VARCHAR(10),DATEPART(DAY,@END)-1) + ' Day(s) ' +
       RIGHT(CONVERT(VARCHAR(10),100+DATEPART(HOUR, @END)),2) + ':' +
       RIGHT(CONVERT(VARCHAR(10),100+DATEPART(MINUTE, @END)),2) + ':' +
       RIGHT(CONVERT(VARCHAR(10),100+DATEPART(SECOND, @END)),2)

If you don't need to format time part:

SELECT CONVERT(VARCHAR(10),DATEPART(DAY,@END)-1) + ' Day(s) ' +
       CONVERT(VARCHAR(10),DATEPART(HOUR, @END)) + '  Hour(s)' +
       CONVERT(VARCHAR(10),DATEPART(MINUTE, @END)) + ' Minute(s)' +
       CONVERT(VARCHAR(10),DATEPART(SECOND, @END)) + ' Second(s)'

score 0 · Answer 5 · answered Oct 30 '17 at 07:01

0

DECLARE @Seconds INT = 86200;
SELECT CONVERT(VARCHAR(15), 
CAST(CONVERT(VARCHAR(12), @Seconds / 60 / 60 % 24)
+':'+ CONVERT(VARCHAR(2), @Seconds / 60 % 60)
+':'+ CONVERT(VARCHAR(2), @Seconds % 60) AS TIME), 100) AS [HH:MM:SS (AM/PM)]

answered Oct 30 '17 at 07:01

Rajiv Singh

958
1
9
14

SQL server, Converting Seconds to Minutes, Hours, Days

5 Answers5

Linked

Related