Convert Seconds to Hours and Minutes

Question

I have read all the posts on StackOverflow about converting an integer from seconds to hours and minutes.
I am using this statement to achieve the results below:

SELECT EventDate, OriginalSeconds, 
LEFT(CONVERT(VARCHAR(5),
DATEADD(second, OriginalSeconds, 0), 108), 5)

I get the following result:

EventDate   Seconds     Converted Time  SHOULD BE
01/13/2011  4860001     06:00           13:30
01/13/2011  4860001     06:00           13:30
01/14/2013  3960001     20:00           11:00
02/03/2011  3960001     20:00           11:00

The only problem, of course, is that the converted Time column has not been calculated correctly. (I placed the correct result in the SHOULD BE column for your re:.) I am imagining that it is because the seconds column actually has extraneous data at the end. But isn't the LEFT function fixing that?

The SHOULD BE column is what I would like to achieve. What am I doing wrong? I was looking for a simple solution without a function, but not sure I can achieve that.

Answer 1

Neither this question nor this answer make sense to me, so I am providing my own solution which does the following:

Convert an integer (representing seconds) into hours, minutes and seconds

First attempt

My solution is given in the field named MySolution below -

with ORIGINAL_DATA as (

    select
        EventDate, OriginalSeconds
    from (
        values
        ('01/13/2011', 4860001),
        ('01/13/2011', 4860001),
        ('01/14/2013', 3960001),
        ('02/03/2011', 3960001)
    ) as rawData(EventDate, OriginalSeconds)
)

SELECT 
    EventDate, 
    OriginalSeconds, 
    LEFT(CONVERT(VARCHAR(5),DATEADD(second, OriginalSeconds, 0), 108), 5) as SolutionFromQuestion,
    LEFT(CONVERT(VARCHAR(5), DATEADD(second, OriginalSeconds/100, 0), 108), 5) as SolutionFromAnswer,
    LEFT(CONVERT(VARCHAR(8),DATEADD(second, OriginalSeconds, 0), 108), 8) as MySolution,
    (OriginalSeconds / 60) as [Minutes],
    (OriginalSeconds / 60 / 60) as [Hours],
    DATEADD(second, OriginalSeconds, 0) as [Timestamp by adding OriginalSeconds to 0]
from ORIGINAL_DATA
;

This yields the results below -

Discussion

All responses - the question, the accepted answer, and my answer - use the code DATEADD(second, OriginalSeconds, 0). This code starts with the "zero date" and adds the number of seconds we are interested in (OriginalSeconds).

I have put just that snippet of code into the last column. You can see from the last column of results that the dates calculated this way are in Februrary of 1900, ie. the OriginalSeconds equates to well over 30 days' worth of time.

I have also put a simple division of the OriginalSeconds integer by 60 - to show how many minutes we should expect (in Minutes), and then divided by 60 again - to show how many hours we should expect (in Hours) - and it's clear - those OriginalSeconds integers relate to over 1,000 hours of time each ... so I have no idea what on earth this question is going on about when it says the expected values to be seen are "13:30". The question asks for hours and minutes. The correct values should be like 1350:00.

Following the same approach as the accepted answer, but extending the string to 8 characters, yields hours, minutes and seconds. My solution shows similar values to those in the question, but includes the seconds component, viz. "06:00:01" - this is the time part of the datetime that was calculated, but ignoring the whole-day portion of the result ... so technically this is still not correct. Like I said, the correct answer should show 1,350 hours for some of the rows.

Final Solution

The final solution provides each of the following:

• Convert an integer representing seconds to:
  • days, hours, minutes, seconds
  • hours, minutes, seconds

See the columns:

• FinalSolutionWithDays
• FinalSolutionWithoutDays

Code below:

with ORIGINAL_DATA as (

    select
        EventDate, OriginalSeconds
    from (
        values
        ('01/13/2011', 4860001),
        ('01/13/2011', 4860001),
        ('01/14/2013', 3960001),
        ('02/03/2011', 3960001)
    ) as rawData(EventDate, OriginalSeconds)
)

select
    EventDate,
    OriginalSeconds,
    LEFT(CONVERT(VARCHAR(8),DATEADD(second, OriginalSeconds, 0), 108), 8) as TimePart,

    DATEADD(second, OriginalSeconds, 0) as FullTimestamp,

    datediff(day, 0, DATEADD(second, OriginalSeconds, 0)) as DaysPart,

    cast(
        datediff(day, 0, DATEADD(second, OriginalSeconds, 0)) -- days part
        as varchar(32)
        )
    + ' days ' 
    + LEFT(CONVERT(VARCHAR(8),DATEADD(second, OriginalSeconds, 0), 108), 8) -- time part
    as FinalSolutionWithDays,

    datediff(hour, 0, DATEADD(second, OriginalSeconds, 0)) as HoursTotal,

    right(LEFT(CONVERT(VARCHAR(8),DATEADD(second, OriginalSeconds, 0), 108), 8), 5) as MinutesAndSeconds,

    cast(
        datediff(hour, 0, DATEADD(second, OriginalSeconds, 0)) -- HoursTotal
        as varchar(32))
    + ':'
    + right(LEFT(CONVERT(VARCHAR(8),DATEADD(second, OriginalSeconds, 0), 108), 8), 5) -- MinutesAndSeconds
    as FinalSolutionWithoutDays

from ORIGINAL_DATA

And here are the results:

Again .. no idea if I'm off the planet, because everyone else seems to know what they're talking about, but I thought if you wanted to convert an integer representing seconds into hours and minutes, etc, you should be seeing answers like I've given here.

Answer 2

The correct statement (using your code) is

SELECT EventDate, OriginalSeconds, 
LEFT(CONVERT(VARCHAR(5),
DATEADD(second, OriginalSeconds/100, 0), 108), 5)

This gives 13:30 for 4860001

You needed to tell us what part of the original OriginalSeconds column wasn't seconds.

DATEADD(second, @secs/100, 0) turns 4860001 into 1900-01-01 13:30:00.000

CONVERT(VARCHAR(5), 1900-01-01 13:30:00.000, 108) turns it into 13:30

The Left Is probably not needed. I just tried it with 9000001 and got a result of 01:00. So the formula wraps to 24 hours