CODEWITHSUNDEEP
mysql
Syntax Rommel
Syntax Rommel

Reputation: 942

How to convert seconds to day time

Example:

seconds = 272920

My Expected output = 3Days hours:minutes:seconds

I am trying to convert the seconds to day time.

Right now I am currently converted it to time using these query:

SELECT CONVERT(SEC_TO_TIME(Seconds), CHAR) FROM table;

and my output is 75:48:40 -------> Format(hh:mm:ss)

But my expected value is days, hours, mins, secs

.

Any suggestion is a big help, thanks in advance..

Upvotes: 2

Views: 2713

Answers (3)

Robby Cornelissen
Robby Cornelissen

Reputation: 97152

With some basic arithmetic, using a combination of modulo and integer division operators:

SELECT CONCAT(
    seconds DIV (60 * 60 * 24),
    ' Days, ',
    seconds DIV (60 * 60) % 24,
    ':',
    seconds DIV 60 % 60,
    ':',
    seconds % 60)　FROM table;

With your example input value of 272920:

SELECT CONCAT(
    272920 DIV (60 * 60 * 24),
    ' Days, ',
    272920 DIV (60 * 60) % 24,
    ':',
    272920 DIV 60 % 60,
    ':',
    272920 % 60);

This will yield the following output:

3 Days, 3:48:40

Upvotes: 1

Praveen
Praveen

Reputation: 9345

Try;

SELECT 
CONCAT(
    FLOOR(HOUR(SEC_TO_TIME(272920)) / 24), ' days ',
    MOD(HOUR(SEC_TO_TIME(272920)), 24), 'H:',
    MINUTE(SEC_TO_TIME(272920)), 'Min:',
    second(SEC_TO_TIME(272920)), 'Sec'
)

Upvotes: 2

Dilmah
Dilmah

Reputation: 1127

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).';

For more details look at this below link SQL Server Convert seconds to Days & hours

Upvotes: 1

Related Questions