SQL Server, Converting Seconds to Minutes, Hours, Days

SQL server, Converting Seconds to Minutes, Hours, Days

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

How to convert Seconds to HH:MM:SS using T-SQL

You want to multiply out to milliseconds as the fractional part is discarded.

SELECT DATEADD(ms, 121.25 * 1000, 0)

If you want it without the date portion you can use CONVERT, with style 114

SELECT CONVERT(varchar, DATEADD(ms, 121.25 * 1000, 0), 114)

Sql Server get time since in X Seconds/Minutes/Hours/Days format

I think you want:

select 'Last action was '
       (case when datediff(day, MAX(myDate), GETDATE()) > 0
             then concat(datediff(day, MAX(myDate), GETDATE()), ' day(s) ago')
             when datediff(hour, MAX(myDate), GETDATE()) > 0
             then concat(datediff(hour, MAX(myDate), GETDATE()), ' hours(s) ago')
             when datediff(minute, MAX(myDate), GETDATE()) > 0
             then concat(datediff(minute, MAX(myDate), GETDATE()), ' minute(s) ago')
             when datediff(second, MAX(myDate), GETDATE()) > 0
             then concat(datediff(second, MAX(myDate), GETDATE()), ' second(s) ago')
             else 'Just happened!'
         end)

The case is starting at the largest unit (days) and checking if that applies.

Actually, datediff() may not be what you want. It aligns to calendar days. So, "1 day ago" really means "yesterday" regardless of time. Perhaps a more accurate method would be:

select 'Last action was '
       (case when dateadd(day, 1, MAX(myDate) < GETDATE()
             then concat(datediff(day, MAX(myDate), GETDATE()), ' day(s) ago')
             when dateadd(hour, 1, MAX(myDate)) < GETDATE())
             then concat(datediff(hour, MAX(myDate), GETDATE()), ' hours(s) ago')
             when dateadd(minute, 1, MAX(myDate)) < GETDATE()
             then concat(datediff(minute, MAX(myDate), GETDATE()), ' minute(s) ago')
             when dateadd(second, 1, MAX(myDate)) < GETDATE())
             then concat(datediff(second, MAX(myDate), GETDATE()), ' second(s) ago')
             else 'Just happened!'
         end)

This guarantees that there is at least one entire unit in the past before choosing that unit.

SQL - Seconds to Day, Hour, Minute, Second

How about this:

declare @days int, 
        @dt1 smalldatetime = '2012-03-25 03:24:16', 
        @dt2 smalldatetime = getdate()

set @days = datediff (s, @dt1, @dt2)

SELECT convert(char(10), @days / (60 * 60 * 24)) + ':'
+ convert(char(10), dateadd(s, @days, convert(datetime2, '0001-01-01')), 108)

Result -- 170:20:40:00

See SQL Fiddle with Demo

Converting Seconds into Total Hours:minutes:seconds

Assuming you've got an integral value for tot_secs — floating point will require some judicious use of round(), ceil() and/or floor(), but you can try something like this:

select hours    = ( t.tot_secs / 3600 )      , -- hours   is total seconds / (secs/hr)
       minutes  = ( t.tot_secs % 3600 ) / 60 , -- minutes is whatever's left over / 60
       seconds  =   t.tot_secs % 60 , -- whatever's left over is seconds
       hh_mm_ss =                    convert(varchar,   t.tot_secs / 3600 )
                + ':' + right( '0' + convert(varchar, ( t.tot_secs % 3600 ) / 60 ) , 2 )
                + ':' + right( '0' + convert(varchar,   t.tot_secs          % 60 ) , 2 )
from ( select tot_secs = 2652819
     ) t

The above query yields

hours minutes seconds hh_mm_ss
----- ------- ------- --------
 736     53      39   736:53:39

SQL Server : convert seconds into hours

You need to divide by 3600 but you need to be careful to avoid integer division. Just add .0 to your divisor.

declare @Seconds int = 27900

select [hours] =  convert(decimal(7,2), @Seconds / 3600.0)

SQL convert Seconds to Minutes to Hours

With the help of Steoleary i have managed a solution

DECLARE @SecondsToConvert int
SET @SecondsToConvert = (SELECT (SUM(DATEDIFF(hour,InviteTime,EndTime) * 3600) + SUM(DATEDIFF(minute,InviteTime,EndTime) * 60) + SUM(DATEDIFF(second,InviteTime,EndTime) * 1)) AS [Seconds] 
 FROM [LcsCDR].[dbo].[SessionDetailsView]
WHERE FromUri LIKE '%robert%'
AND (CAST([InviteTime] AS date)) BETWEEN '2012-12-27' AND '2013-01-28'
AND MediaTypes = '16'
GROUP BY FromUri)

-- Declare variables
 DECLARE @Hours int
 DECLARE @Minutes int
 DECLARE @Seconds int

-- Set the calculations for hour, minute and second
SET @Hours = @SecondsToConvert/3600
SET @Minutes = (@SecondsToConvert % 3600) / 60
SET @Seconds = @SecondsToConvert % 60

SELECT COUNT(*) AS 'Aantal gesprekken'
,FromUri AS 'Medewerker'
,@Hours AS 'Uren' ,@Minutes AS 'Minuten' , @Seconds AS 'Seconden'
 FROM [LcsCDR].[dbo].[SessionDetailsView]
WHERE FromUri LIKE '%robert%'
AND (CAST([InviteTime] AS date)) BETWEEN '2012-12-27' AND '2013-01-28'
AND MediaTypes = '16'
GROUP BY FromUri

As a result, i now get the accurate time.

302 robert  28  19  56

28 hours, 19 minutes and 56 seconds, just like it should be :)

Efficient way to convert second to minute and seconds in sql server 2005

With hours:

SELECT CONVERT(CHAR(8),DATEADD(second,90,0),108)
00:01:30

Ignoring hours:

SELECT RIGHT(CONVERT(CHAR(8),DATEADD(second,90,0),108),5)
01:30

Convert seconds to days, hours, minutes in Bigquery

Below is for BigQuery Standard SQL

#standardSQL
select seconds, 
  regexp_replace(
    cast(time(ts) as string), 
    r'^\d\d', 
    cast(extract(hour from time(ts)) + 24 * unix_date(date(ts)) as string)
  ) as option1, 
  format(
    '%i day %i hour %i minute %i second', 
    unix_date(date(ts)), 
    extract(hour from time(ts)), 
    extract(minute from time(ts)), 
    extract(second from time(ts))
  ) as option2
from `project.dataset.table`, 
unnest([timestamp_seconds(seconds)]) ts   

if to apply to sample data from your question as in

with `project.dataset.table` AS (
  select 100000 seconds union all
  select 200000 union all
  select 300000
)   

the output is

---

Related Topics