How to Convert Milliseconds to Time(Hh:Mm:Ss) in Oracle

How to convert milliseconds to "hh:mm:ss" format?

You were really close:

String.format("%02d:%02d:%02d", 
TimeUnit.MILLISECONDS.toHours(millis),
TimeUnit.MILLISECONDS.toMinutes(millis) -
TimeUnit.HOURS.toMinutes(TimeUnit.MILLISECONDS.toHours(millis)), // The change is in this line
TimeUnit.MILLISECONDS.toSeconds(millis) -
TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(millis)));

You were converting hours to millisseconds using minutes instead of hours.

BTW, I like your use of the TimeUnit API :)

Here's some test code:

public static void main(String[] args) throws ParseException {
long millis = 3600000;
String hms = String.format("%02d:%02d:%02d", TimeUnit.MILLISECONDS.toHours(millis),
TimeUnit.MILLISECONDS.toMinutes(millis) - TimeUnit.HOURS.toMinutes(TimeUnit.MILLISECONDS.toHours(millis)),
TimeUnit.MILLISECONDS.toSeconds(millis) - TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(millis)));
System.out.println(hms);
}

Output:

01:00:00

I realised that my code above can be greatly simplified by using a modulus division instead of subtraction:

String hms = String.format("%02d:%02d:%02d", TimeUnit.MILLISECONDS.toHours(millis),
TimeUnit.MILLISECONDS.toMinutes(millis) % TimeUnit.HOURS.toMinutes(1),
TimeUnit.MILLISECONDS.toSeconds(millis) % TimeUnit.MINUTES.toSeconds(1));

Still using the TimeUnit API for all magic values, and gives exactly the same output.

Oracle: add milliseconds to my timestamp object

You add time to a timestamp by adding an interval. Intervals can go down to seconds only, but an interval of 0.001 seconds is a milliseconds interval which you can multiply with the desired value. E.g:

select systimestamp + interval '0.001' second * :millisecs from dual;

Oracle Convert Seconds to Hours:Minutes:Seconds

If you're just looking to convert a given number of seconds into HH:MI:SS format, this should do it

SELECT 
TO_CHAR(TRUNC(x/3600),'FM9900') || ':' ||
TO_CHAR(TRUNC(MOD(x,3600)/60),'FM00') || ':' ||
TO_CHAR(MOD(x,60),'FM00')
FROM DUAL

where x is the number of seconds.

Using Oracle to_date function for date string with milliseconds

An Oracle DATE does not store times with more precision than a second. You cannot store millisecond precision data in a DATE column.

Your two options are to either truncate the string of the milliseconds before converting it into a DATE, i.e.

to_date( substr('23.12.2011 13:01:001', 1, 19), 'DD.MM.YYYY HH24:MI:SS' )

or to convert the string into a TIMESTAMP that does support millisecond precision

to_timestamp( '23.12.2011 13:01:001', 'DD.MM.YYYY HH24:MI:SSFF3' )

SQL Convert Milliseconds to Days, Hours, Minutes

You can do the calculation explicitly. I think it is:

select floor(msvalue / (1000 * 60 * 60 * 24)) as days,
floor(msvalue / (1000 * 60 * 60)) % 24 as hours,
floor(msvalue / (1000 * 60)) % 60 as minutes

Note: Some databases use mod instead of %.

How to convert Milliseconds to "X mins, x seconds" in Java?

Use the java.util.concurrent.TimeUnit class:

String.format("%d min, %d sec", 
TimeUnit.MILLISECONDS.toMinutes(millis),
TimeUnit.MILLISECONDS.toSeconds(millis) -
TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(millis))
);

Note: TimeUnit is part of the Java 1.5 specification, but toMinutes was added as of Java 1.6.

To add a leading zero for values 0-9, just do:

String.format("%02d min, %02d sec", 
TimeUnit.MILLISECONDS.toMinutes(millis),
TimeUnit.MILLISECONDS.toSeconds(millis) -
TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(millis))
);

If TimeUnit or toMinutes are unsupported (such as on Android before API version 9), use the following equations:

int seconds = (int) (milliseconds / 1000) % 60 ;
int minutes = (int) ((milliseconds / (1000*60)) % 60);
int hours = (int) ((milliseconds / (1000*60*60)) % 24);
//etc...


Related Topics



Leave a reply



Submit