SQL Convert Milliseconds to Days, Hours, Minutes

SQL Convert Milliseconds to Days, Hours, Minutes

You can do the calculation explicitly. I think it is:

select floor(msvalue / (1000 * 60 * 60 * 24)) as days,
       floor(msvalue / (1000 * 60 * 60)) % 24 as hours,
       floor(msvalue / (1000 * 60)) % 60 as minutes

Note: Some databases use mod instead of %.

Converting Milliseconds to Days, hours, minutes and seconds

It's too big for DATEADD which only accepts an int.

Break it into two parts: seconds, then milliseconds.

SELECT CONVERT(TIME,
          DATEADD(ms, SUM(duration/10000 % 1000),
          DATEADD(ss, SUM(duration/10000000), 0)))
FROM tblMediaFileProperties 

And if your total duration goes above 1 day, you can use this to get the days and hr:min:sec:ms separately. It's a matter of cast and string concat if you actually want the result in textual form.

declare @duration bigint
set @duration = 1230000000
SELECT @duration/10000/1000/60/60/24 DAYS,
       CONVERT(TIME,
          DATEADD(ms, SUM(@duration/10000 % 1000),
          DATEADD(ss, SUM(@duration/10000000), 0))) HR_MIN_SEC

how to convert milliseconds,seconds,minutes,hours into days in sql server 2005?

In integer arithmetic, using the values:

1 second ==      1,000 milliseconds;
1 minute ==     60,000 milliseconds;
1 hour   ==  3,600,000 milliseconds;
1 day    == 86,400,000 milliseconds; 

Convert seconds, minutes, and hours to milliseconds.

Then find integer and fractional days:

integral days (in units)         = total_milliseconds / 86,400,000
fractional days (in milliseconds = total_milliseconds % 86,400,000

And there you have it.

Hive/SQL Error when converting milliseconds to DDDD:HH:mm:ss

The logic is simple math. BIGINT timestamp is the number of seconds or milliseconds passed from Unix Epoch (1970-01-01 00:00:00 UTC).

To get milliseconds part use (ts % 1000) - returns reminder after division by 1000

To get total whole seconds passed, use (ts div 1000) - returns integer part, all other figures will be calculated from this number: days, hours, minutes, seconds.

days: (ts div 1000) div 86400 - returns integer part after division of total seconds by number of seconds in a day

To get hours left after whole days calculation: take reminder after days calculation ((ts div 1000) % 86400) and divide by number of seconds in hour, take integer part (((ts div 1000) % 86400) div 3600)

And so on.

Demo:

with your_data as (
select 1 id, bigint(2513702864) ts union all
select 2, bigint(17259) union all
select 3,bigint(127259) union all
select 4,bigint(1272) union all
select 5,bigint(127)
)

select --format output as required. For example days:hours:minutes:seconds.millis
       concat(days,':',hours,':',minutes,':',seconds,'.',millis)
from
(
select ((ts div 1000) div 86400) days, --number of whole days
       lpad(((ts div 1000) % 86400) div 3600, 2, 0) hours, --whole hours left
       lpad((((ts div 1000) % 86400) % 3600) div 60, 2, 0) minutes, --whole minutes left
       lpad((((ts div 1000) % 86400) % 3600) % 60, 2, 0) seconds, --seconds left
       (ts % 1000) as millis
  from your_data
)s

Result:

1 29:02:15:02.864 --29 whole days, 2 hours, 15 minutes, 2 seconds, 864 millis
2 0:00:00:17.259 --17 whole seconds and 259 millis
3 0:00:02:07.259 --two whole minutes, 7 seconds and 259 millis
4 0:00:00:01.272 --one whole second and millis
5 0:00:00:00.127 --we have only milliseconds

Now you can see the difference between this calculation and what from_unixtime returns.
For record id=1 the number of whole days is 29. Why from_unixtime returns 30 (for pattern 'D')? Because 29 whole days passed and we are 2 hrs 15 min 2 sec 864 mi in a new day 30. In other words, from_unixtime returns timestamp formatted and calculation in my query returns interval formatted, "day in a year" and "whole days passed from" are different things.

Hope, now it is as clear as a day.

See also similar question: https://stackoverflow.com/a/57497316/2700344

And if you need to convert bigint timestamp in milliseconds to string with milliseconds preserved (yyyy-MM-dd HH:mm:ss.SSS) use this:

select concat(from_unixtime(ts div 1000), '.', (ts  % 1000)) as timestamp_with_millis
  from (select bigint(2513702864)  as ts) s

Result:

1970-01-30 02:15:02.864

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

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