CODEWITHSUNDEEP
sqlsql-servertimeduration
Glenn
Glenn

Reputation: 17

SQL: Convert duration in microseconds to DD:HH:MM:SS

I am working with data containing [duration] in microseconds, some examples:

5982312624, 181240328664, 2981750461, 993806305107, 3015857413, 283850264906, 5987597545, 240770859583

I converted this to number of hours, minutes and seconds as follows:

(CONVERT(VARCHAR(10), Dateadd(second, (((([duration]/10^7)/3600*60)/1000000.0)*60),0),114)) AS [HH:MM:SS]

The problem I am facing is when this exeeds 24 hours it does not register correctly.

Question: I would either like to add a day in here for example: DD:HH:MM:SS -> 01:23:04:33 or just continue the count of hours example: 45:12:52.

Hope anyone can help. Thanks in advance.

Upvotes: 1

Views: 3854

Answers (2)

blu3drag0n
blu3drag0n

Reputation: 71

I think the answer above causes a little bit of a headache and is not obvious how to tweak for most I guess. It's far easier to work with FLOOR() and FORMAT().

Example for [DDDD:HH:MM:SS]:

DECLARE @Table TABLE (id int identity, duration bigint)
INSERT @testTable VALUES
(5982312624),(181240328664),(2981750461),(993806305107),
(3015857413),(283850264906),(5987597545),(240770859583)

DECLARE @micro int = 1000000

SELECT id, [HHHH:MM:SS] = 
FORMAT(FLOOR(duration/@micro/86400), '0000')+ ':' +    --Convert to total days
FORMAT(FLOOR(duration/@micro/3600) % 24, '00')+ ':' +  --Convert to total hours and modulo the days
FORMAT(FLOOR(duration/@micro/60) % 60, '00')+ ':' +    --Convert to total minutes and modulo the hours
FORMAT(FLOOR(duration/@micro)  % 60, '00')             --Convert to total seconds and modulo the minutes
FROM
@testTable

enter image description here

Example for [HHHH:MM:SS]:

DECLARE @durationTable TABLE (id int identity, duration bigint)
INSERT @durationTable VALUES
(5982312624),(181240328664),(2981750461),(993806305107),
(3015857413),(283850264906),(5987597545),(240770859583)

DECLARE @micro int = 1000000

SELECT id, [HHHH:MM:SS] = 
FORMAT(FLOOR(duration/@micro/3600), '0000')+ ':' +     --Convert to total hours
FORMAT(FLOOR(duration/@micro/60) % 60, '00')+ ':' +    --Convert to total minutes and modulo the hours
FORMAT(FLOOR(duration/@micro)  % 60, '00')             --Convert to total seconds and modulo the minutes
FROM
@durationTable

enter image description here

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

Assuming a duration can't be longer than 100 days, which I hope it can't if you're recording microseconds, then given this sample data:

CREATE TABLE dbo.Source(id int identity, duration bigint);

INSERT dbo.Source(duration) VALUES
(5982312624),(181240328664),(2981750461),(993806305107),
(3015857413),(283850264906),(5987597545),(240770859583);

One way is to determine the day of year after adding seconds (not microseconds, which will overflow) to any arbitrary January 1st, and prepend the number of days onto the conversion to time:

SELECT id, duration, 
  [DD:HH:MM:SS] = RIGHT('0' + RTRIM(DATEPART(DAYOFYEAR, dt) - 1), 2)
    + ':' + CONVERT(char(8), dt, 108)
  FROM dbo.Source 
  CROSS APPLY 
  (
    VALUES(DATEADD(SECOND, duration/1000000.0, '20000101'))
  ) AS d(dt);

Results:

id duration DD:HH:MM:SS
1 5982312624 00:01:39:42
2 181240328664 02:02:20:40
3 2981750461 00:00:49:41
4 993806305107 11:12:03:26
5 3015857413 00:00:50:15
6 283850264906 03:06:50:50
7 5987597545 00:01:39:47
8 240770859583 02:18:52:50

If it can be more than 100 days (or more than a year), you can make this slight change:

SELECT id, duration, 
  [DDDD:HH:MM:SS] = RIGHT('000' + RTRIM(DATEDIFF(DAY, '20000101', dt)), 4)
    + ':' + CONVERT(char(8), dt, 108)
  FROM dbo.Source 
  CROSS APPLY 
  (
    VALUES(DATEADD(SECOND, duration/1000000.0, '20000101'))
  ) AS d(dt);

Results:

id duration DDDD:HH:MM:SS
1 5982312624 0000:01:39:42
2 181240328664 0002:02:20:40
3 2981750461 0000:00:49:41
4 993806305107 0011:12:03:26
5 3015857413 0000:00:50:15
6 283850264906 0003:06:50:50
7 5987597545 0000:01:39:47
8 240770859583 0002:18:52:50

And for completeness, if you want all the hours added together instead of separating days, it's actually slightly more complicated:

SELECT id, duration, 
    [HHHH:MM:SS] = RIGHT('000' + CONVERT(varchar(11), 
      24 * DATEDIFF(DAY, '20000101', dt) + DATEPART(HOUR, dt)), 4)
      + RIGHT(CONVERT(char(8), dt, 108), 6)
  FROM dbo.Source 
  CROSS APPLY 
  (
    VALUES(DATEADD(SECOND, duration/1000000.0, '20000101'))
  ) AS d(dt);

Results:

id duration HHHH:MM:SS
1 5982312624 0001:39:42
2 181240328664 0050:20:40
3 2981750461 0000:49:41
4 993806305107 0276:03:26
5 3015857413 0000:50:15
6 283850264906 0078:50:50
7 5987597545 0001:39:47
8 240770859583 0066:52:50

If it can be more than 1,000 days, well, perhaps you should consider storing start and end as datetime instead of duration in microseconds.

Upvotes: 4

Related Questions