Convert Seconds to HH:MM:SS

Hello experts,

I have a column that has seconds in it.  I want to write something in my select statement that will convert this to HH:MM:SS?  Please advise.  

This answer is needed urgently! Thanks for your time.



P.S.  If this is of any help, this is what I have so far.
(Column1/3600) this would give me hours.  Then I need the integer function in Oracle (which I only know in MS SQL).  So it would be something like  
INT(Column1/3600)||":" ||
((INT(Column1/3600)) - (Column1/3600)/60) || ":" ||
INT(((INT(Column1/3600)) - (Column1/3600)/60)) - ((INT(Column1/3600)) - (Column1/3600)/60)
LVL 1
jjrr007Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

raopsnCommented:
Select
Trunc(Column1 / 3600) || ':' || Trunc(Mod(Column1, 3600) / 60)  || ':' Mod(Column1, 60) as Time
From <table>

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NicksonKohCommented:
Hi jjrr007,

If your column is a date string, you can always use the to_char function to format the date into a date string format that you require. Read below extract.



TO_CHAR converts date of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, date is converted to a VARCHAR2 value as follows:

w    DATE is converted to a value in the default date format.

w    TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE are converted to values in the default timestamp format.

w    TIMESTAMP WITH TIME ZONE is converted to a value in the default timestamp with time zone format.

The 'nlsparams' specifies the language in which month and day names and abbreviations are returned. This argument can have this form:

'NLS_DATE_LANGUAGE = language'

If you omit nlsparams, this function uses the default date language for your session.

Syntax

>ÄÄTO_CHARÄÄÄ(dateÄÂÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÂÄ)ÄÄ><
                   ÀÄ, fmtÄÂÄÄÄÄÄÄÄÄÄÄÄÄÄÂÄÙ
                           ÀÄ,'nlsparam'ÄÙ

There are three TO_CHAR functions: one for dates, one for numbers and one for MLS Labels. Each accepts a variable of the specified type and then converts it into an equivalent character string. With each type of datatype, there is a format string that can be specified to produce the output format.

Date Format Specifiers

Format Model    Description
CC, SCC    Century (S prefixes BC dates with a minus sign)
YYYY, SYYYY    Year (S prefixes BC dates with a minus sign)
IYYY    Year based on ISO standard
YYY, YY, Y    Last three, two or one digits of the year
IYY, IY, I    Last three, two or one digits of the ISO year
Y,YYY    (Four Y's with comma) put a comma in the year (1,995)
YEAR, SYEAR    Year spelled out (S prefixes BC dates with a minus sign)
RR    Last two digits of year in another century (allows for year 2000)
BC, AD    BC or AD indicator
B.C., A.D.    BC or AD indicators with periods
Q    Numeric quarter of the year (1-4 with Jan-Mar=1)
MM    2 number month (Jan = 01)
RM    Roman numeral month
MONTH    Name of month spelled out (upper case - month is upper case)
MON    abbreviated name of month (upper case - month is upper case)
WW    Week of the year (1-53)
IW    Week of the year (1-52, 1-53) based on ISO standard
W    Week of month (1-5)
DDD    day of year (1-366) (Don't forget leap years)
DD    day of month (1-31)
D    day of week (1-7)
DAY    Name of day (upper case, day is upper case)
DY    Abbreviated name of day
J    Julian day (Number of days since January 1, 4712 BC)
AM,PM    Meridian indicator
A.M., P.M.    Meridian indicator with periods.
HH, HH12    Hour of day (0-12)
HH24    Use 24 hour clock for hours (1-24)
MI    Minute (0-59)
SS    Second (0-60)
SSSSS    (five S's) seconds past midnight. (0-86399)
None    Date must be in the format 'DD-MON-YY';

For dates, if format specifier is J then the input must be a number.

Numeric Format Models

Element    Example    Description
9    9999    significant digit
0    0999    leading zero instead of a blank
$    $999    leading dollar sign
B    B999    leading blank instead of a zero
MI    999MI    trailing minus sign
S    S999    leading plus or minus sign
PR    999PR    angle brackets for negative values
D    99D99    decimal character
G    9G99    group separator
C    C999    ISO currency symbol
L    L999    local currency symbol
,    9,999    comma
.    99.99    period
V    999V99    10n multiplier; n is the number of 9's after the V
EEEE    9.99EEEE    scientific notation (you must have 4 E's)
RN, rn    RN    Upper or lower case roman numeral

MLS Label Formats

These are specified in the Trusted ORACLE7 Server Administrator's Guide. The TO_CHAR with MLS Labels is only available with Trusted Oracle.

PL/SQL Example

Notice that there are two blanks between month and day and a leading zero for the fifth day:

TO_CHAR (SYSDATE, 'Month DD, YYYY')                   ==> 'February  05, 1994'

Use the FM fill mode element to suppress blanks and zeros:

TO_CHAR (SYSDATE, 'FMMonth DD, YYYY')                 ==> 'February 5, 1994'

Note the case difference on the month abbreviations of the next two samples. You get exactly what you ask for with Oracle date formats. Note also that the case of the day suffix depends on the case of the 'DD' or 'dd', not the 'th'.

TO_CHAR (SYSDATE, 'MON DDth, YYYY')                   ==> 'FEB 05TH, 1994'
TO_CHAR (SYSDATE, 'fmMon DDth, YYYY')                 ==> 'Feb 5TH, 1994'
TO_CHAR (SYSDATE, 'fmMon ddth, YYYY')                 ==> 'Feb 5th, 1994'

Show the day of year, the month, and the week for the date:

TO_CHAR (SYSDATE, 'DDD DD D ')                        ==> '036 05 7'
TO_CHAR (SYSDATE, 'fmDDD DD D ')                      ==> '36 5 7'

Some fancy formatting for reporting purposes:

TO_CHAR (SYSDATE, '"In month "RM" of year "YEAR')     ==>
   'In month II   of year NINETEEN NINETY FOUR'

Here are some examples of TO_CHAR for number conversion:

TO_CHAR (564.70, '$999.9')                            ==>  $564.7
TO_CHAR (564.70, '$0000999.9')                        ==> $0000564.7

SQL Example

The following example uses this table:

CREATE TABLE my_tab (
   ts_col TIMESTAMP,
   tsltz_col TIMESTAMP WITH LOCAL TIME ZONE,
   tstz_col TIMESTAMP WITH TIME ZONE);

The example shows the results of applying TO_CHAR to different TIMESTAMP datatypes. The result for a TIMESTAMP WITH LOCAL TIME ZONE column is sensitive to session time zone, whereas the results for the TIMESTAMP and TIMESTAMP WITH TIME ZONE columns are not sensitive to session time zone:

ALTER SESSION SET TIME_ZONE = '-8:00';
INSERT INTO my_tab VALUES (  
   TIMESTAMP'1999-12-01 10:00:00',
   TIMESTAMP'1999-12-01 10:00:00',
   TIMESTAMP'1999-12-01 10:00:00');
INSERT INTO my_tab VALUES (
   TIMESTAMP'1999-12-02 10:00:00 -8:00',
   TIMESTAMP'1999-12-02 10:00:00 -8:00',
   TIMESTAMP'1999-12-02 10:00:00 -8:00');

SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'),
   TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM')
   FROM my_tab;

TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI:
------------------------------ -------------------------------------
01-DEC-1999 10:00:00           01-DEC-1999 10:00:00.000000 -08:00
02-DEC-1999 10:00:00           02-DEC-1999 10:00:00.000000 -08:00

SELECT SESSIONTIMEZONE,
   TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF')
   FROM my_tab;

SESSION TO_CHAR(TSLTZ_COL,'DD-MON-YYYY
------- ------------------------------
-08:00  01-DEC-1999 10:00:00
-08:00  02-DEC-1999 10:00:00

ALTER SESSION SET TIME_ZONE = '-5:00';
SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'),
   TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM')
   FROM my_tab;

TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI:
------------------------------ -------------------------------------
01-DEC-1999 10:00:00           01-DEC-1999 10:00:00.000000 -08:00
02-DEC-1999 10:00:00           02-DEC-1999 10:00:00.000000 -08:00

SELECT SESSIONTIMEZONE,
   TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF')
   FROM my_tab;

SESSION TO_CHAR(TSLTZ_COL,'DD-MON-YYYY
------- ------------------------------
-05:00  01-DEC-1999 13:00:00
-05:00  02-DEC-1999 13:00:00


Cheers
Nickson
NicksonKohCommented:
jjrr007,

In short, below is a sample sql for what you need

Select to_char(yourDate, 'HH:MI:SS') from yourTable

Nickson
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

RCorfmanCommented:
This will work for times less than 24 hours.  I think it can be done with intervals too, but I'd have to research that more if you need more than 24 hours.

SQL> select * from udttest;

        ID
----------
        10
       110
      2000
      4800
     10000
     12000

6 rows selected.

SQL> select to_char(trunc(sysdate)+(id/86400),'hh24:mi:ss') as time from udttest;

TIME
---------------------------------------------------------------------------
00:00:10
00:01:50
00:33:20
01:20:00
02:46:40
03:20:00

6 rows selected.

SQL>
RCorfmanCommented:
works with times below 86,400 seconds.  (less than 24 hours).
jjrr007Author Commented:
Nickson,

Thanks for your reply.  I think you are on the right track.  I tried this right before I left from work.  
to_date(column1, 'HH:MI:SS') As column1

The column is already in char format.  Anyways, when I tried this, I got an error message saying that hours should be between 0-12.  Also, I tried HH24 and received a similar error- i.e. the column may have large numbers. Anyways, should I use to_char as you recommended or to_date, and how should I get the hours to work?

Thanks for your time.
raopsnCommented:
You can not use either TO_DATE or TO_CHAR since your data is neither a date type nor a date string
NicksonKohCommented:
convert the string to date with to_date and then to_char to format the date to your required date string format.

E.g. to_char(to_date(yourString, 'theDateFormatOfYourString'), 'HH:MI:SS')

Replace 'theDateFormatOfYourString' with the necessary original date format of the string.
E.g. if it is 12/02/1971 15:20:20 then it should be 'dd/mm/yyyy hh:mi:ss'
raopsnCommented:
>> SSSSS    (five S's) seconds past midnight. (0-86399) <<

The only way I see is to use the SSSSS format and as you can see it has the higher limit of 86399

If your seconds does not exceed that, then you can use the TO_DATE & TO_CHAR
RCorfmanCommented:
I showed an example on how to do this assuming your column is numeric and has values less than 86,400. If you have values larger than this, or if your data isn't numeric, please clarify.
RCorfmanCommented:
As mentioned, my original solution works for seconds up to 86,400, but breaks down after that.
SQL> select seconds,
  2         to_char(trunc(sysdate)+(seconds/86400),'hh24:mi:ss') as time
  3   from test;

   SECONDS TIME
---------- --------------------
        10 00:00:10
       110 00:01:50
      2000 00:33:20
      4800 01:20:00
     10000 02:46:40
     55189 15:19:49
     86399 23:59:59
     86400 00:00:00
     86524 00:02:04
   2000083 03:34:43

10 rows selected.

SQL>
RCorfmanCommented:
If knowing the number of days is important too because the seconds are too large, that is easily added to the data returned.  This is all assuming your column is numeric.

SQL> select seconds,
  2         trunc(seconds/86400) days,
  3         to_char(trunc(sysdate)+(seconds/86400),'hh24:mi:ss') as time
  4   from test;

   SECONDS       DAYS TIME
---------- ---------- --------
        10          0 00:00:10
       110          0 00:01:50
      2000          0 00:33:20
      4800          0 01:20:00
     10000          0 02:46:40
     55189          0 15:19:49
     86399          0 23:59:59
     86400          1 00:00:00
     86524          1 00:02:04
   2000083         23 03:34:43

10 rows selected.

SQL>
jjrr007Author Commented:
Experts RCorfman, raopsn, NicksonKoh


Thanks for your responses.  To answer some of your questions.  Yes, this column does have values over 87,000.  Also, I don't want to display days.  Just hours, minutes and seconds.  So what should I do?


Also, raospn, your very first response may work.  Should I use your first recommendation?

Thanks to all!
jjrr007Author Commented:
raospn,

Your first idea worked.  Thank you very much!  

Select
Trunc(Column1 / 3600) || ':' || Trunc(Mod(Column1, 3600) / 60)  || ':' || Mod(Column1, 60) as Time
From <table>

You are just missing the last ||.  Brilliant!
Geovani LeitaoCommented:
select to_char(to_Date(seconds,'SSSSS'),'HH24:MI:SS') from dual
jjrr007Author Commented:
This question is from 10 years ago.
Geovani LeitaoCommented:
I saw it, but I was looking for it and find out this way to solve it and I think this way is easier too. It can be usefull to somebody.
jjrr007Author Commented:
OK. Thx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.