How to convert a SQL Bignit column into Days:Hours:Minutes:Seconds

Question

How do I convert a sql bignit column in a table into days:hours:minutes:seconds

For example: (below) is the targeted column from the table and two rows of data:

        time_Left
        516542000
        604744000

How do I convert or Cast all the data in the column to display as Days:Hours:Minutes:Seconds?

I've tried to convert it to datetime2, varchar, digit, decimals and datetime and I either get an error message or a string of digits. I'm looking for something to give me a break down of how many days:hours:minutes:Seconds the time_left is or hours:minutes:Seconds.

Without any details nobody can help you. What are those values? Why are you storing what seems to be something like dates as a bigint? What do you expect as output from the values you posted? — Sean Lange, Feb 22 '23 at 14:32
What does `516542000` represent? (0)0 seconds, 20 minutes, 54 hours and 516 days? Something else? — Thom A, Feb 22 '23 at 14:35

score 0 · Answer 1 · answered Feb 22 '23 at 15:00

0

If they are unix timestamps they are seconds from 1/1/1970.

If so, then you can convert with DATEADD(SECOND, 516542000, '1970-01-01').

Also refer to this Convert Datetime to Unix timestamp for the reverse process.

answered Feb 22 '23 at 15:00

Roberto Ferraris

465
3
8

score 0 · Accepted Answer · answered Feb 22 '23 at 15:19

0

If this column contains seconds then you can do it as follows :

select time_left,
  CONCAT(
    FLOOR(time_left/(24*3600)),':',
    FLOOR(time_left%(24*3600)/3600),':', 
    FLOOR(time_left%(24*3600)%3600/60),':', 
    (time_left%(24*3600)%3600)%60 
) as "converted"
from mytable;

FLOOR() to return the largest integer value.

Demo here

answered Feb 22 '23 at 15:19

SelVazi

10,028
2
13
29

Thank you I believe it is 'seconds' showing up in sql. In the application view, the 'Time_Left' column looks like (example) '5 Days 9 Hours 25 Minutes'. A question I have is does the '5978' in the time life conversion =5978 days? In the demo you provided? That amount of days seems to be much larger then what it should be. – user21265764 Feb 24 '23 at 05:11
check the result using this formule : *Days*24*3600 + Hours*3600 + Minutes*60 + Seconds* - example : `select 5978*24*3600 + 11*3600 + 53*60 + 20;` – SelVazi Feb 24 '23 at 07:41

How to convert a SQL Bignit column into Days:Hours:Minutes:Seconds

2 Answers2