Link to home
Start Free TrialLog in
Avatar of jjrr007
jjrr007

asked on

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)
ASKER CERTIFIED SOLUTION
Avatar of raopsn
raopsn

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
jjrr007,

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

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

Nickson
Avatar of RCorfman
RCorfman

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>
works with times below 86,400 seconds.  (less than 24 hours).
Avatar of jjrr007

ASKER

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.
You can not use either TO_DATE or TO_CHAR since your data is neither a date type nor a date string
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'
>> 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
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.
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>
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>
Avatar of jjrr007

ASKER

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!
Avatar of jjrr007

ASKER

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!
select to_char(to_Date(seconds,'SSSSS'),'HH24:MI:SS') from dual
Avatar of jjrr007

ASKER

This question is from 10 years ago.
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.
Avatar of jjrr007

ASKER

OK. Thx