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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
jjrr007,
In short, below is a sample sql for what you need
Select to_char(yourDate, 'HH:MI:SS') from yourTable
Nickson
In short, below is a sample sql for what you need
Select to_char(yourDate, 'HH:MI:SS') from yourTable
Nickson
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),'h h24: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>
SQL> select * from udttest;
ID
----------
10
110
2000
4800
10000
12000
6 rows selected.
SQL> select to_char(trunc(sysdate)+(id
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).
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.
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'
E.g. to_char(to_date(yourString
Replace 'theDateFormatOfYourString
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
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)+(se conds/8640 0),'hh24:m i: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>
SQL> select seconds,
2 to_char(trunc(sysdate)+(se
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)+(se conds/8640 0),'hh24:m i: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>
SQL> select seconds,
2 trunc(seconds/86400) days,
3 to_char(trunc(sysdate)+(se
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>
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!
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!
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!
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,'S SSSS'),'HH 24:MI:SS') from dual
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.
ASKER
OK. Thx
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-YYY
--------------------------
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-
------- --------------------------
-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-YYY
--------------------------
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-
------- --------------------------
-05:00 01-DEC-1999 13:00:00
-05:00 02-DEC-1999 13:00:00
Cheers
Nickson