How to convert miliseconds to hh mm ss format in Oracle

Question

I have a Database table having a column 'time' which has milliseconds stored. I need to convert that in hours:minutes:seconds format. Can anyone please help how to do that? I think it requires a function. I tried one , but it only works for hours less than 24 hours. I need it to convert greater than 24 hours as well. Note: the datatype of the Time column is Varchar2.

Thanks in advance..

score 0 · Answer 1 · edited May 23 '17 at 10:27

0

If it is mySql, SELECT SEC_TIME(seconds/1000); does what you want exactly, but if it is oracle sql, here is a link: Oracle Convert Seconds to Hours:Minutes:Seconds Adding 1000 in the denominator of solution in the link does task of converting milliseconds to hh:mm:ss upto 9999 hours. Hope it helps! Thanks

edited May 23 '17 at 10:27

Community

1
1

answered Oct 14 '16 at 06:12

zealouscoder

96
2
3
13

Thanks for your feedback! i found below code on the link you mentioned: – vinaya pathak Oct 18 '16 at 07:07
SELECT TO_CHAR(TRUNC(x/3600),'FM9900') || ':' || TO_CHAR(TRUNC(MOD(x,3600)/60),'FM00') || ':' || TO_CHAR(MOD(x,60),'FM00') FROM DUAL – vinaya pathak Oct 18 '16 at 07:07
But it is just conversion of seconds to hh:mm:ss . I need milliseconds to hh:mm:ss. can you please let me know how to add 1000 in denominator of solution? i didn't get that. – vinaya pathak Oct 18 '16 at 07:10

score 0 · Answer 2 · edited Nov 17 '17 at 16:18

0

milliseconds to hh:mm:ss

SELECT TO_CHAR(TRUNC(s/3600000),'FM9900') || ':' || TO_CHAR(TRUNC(MOD(s,3600000)/60000),'FM00') 
|| ':' ||TO_CHAR(MOD(s,60000),'FM00')
FROM DUAL ;

edited Nov 17 '17 at 16:18

O'Neil

3,790
4
16
30

answered Nov 17 '17 at 15:56

Anass EL

11
1
7

How to convert miliseconds to hh mm ss format in Oracle

2 Answers2