0

In my oracle db i have 2 VARCHAR2 representation of duration (HH:MM:SS), i need to retrieve it with java and subtract it.

Example:

Duration_1| Duration_2
 00:14:12   00:14:08

My logic is something like:

resultset.getString("Duration_1") - resultset.getString("Duration_2");  //tried this not working

My expected result is: 00:00:04

How can i achieve that?

hades
  • 4,294
  • 9
  • 46
  • 71

4 Answers4

1

Joda-Time

You can not get the difference like subtracting the strings. I would suggest using Joda-Time.

Refer to this Question:

How to find difference between two Joda-Time DateTimes in minutes

Community
  • 1
  • 1
KhAn SaAb
  • 5,248
  • 5
  • 31
  • 52
1

In Oracle you can convert the values to INTERVALs and then subtract them:

Oracle Setup:

CREATE TABLE table_name ( interval1, interval2 ) AS
SELECT '00:14:12', '00:14:08' FROM DUAL

Query 1 - Returning an interval:

SELECT TO_DSINTERVAL( '0 '||interval1 ) - TO_DSINTERVAL( '0 '||interval2 ) AS difference
FROM   table_name;

Results:

     difference
-------------------
+00 00:00:04.000000

Query 2 - Returning an HH24:MI:SS formatted string:

SELECT SUBSTR( TO_DSINTERVAL( '0 '||interval1 ) - TO_DSINTERVAL( '0 '||interval2 ), 12, 8 ) AS difference
FROM   table_name;

Results:

difference
----------
 00:00:04
MT0
  • 143,790
  • 11
  • 59
  • 117
  • I think OP wants to do it in Java and not in database. – Lalit Kumar B Jan 05 '16 at 10:02
  • If the OP wants that arbitrary restriction then fine - however, this is a simple solution that will do it in the DB and mean they only need to return a single interval value in the result set rather than two string values and may be of value to other people looking at this question who do not have that arbitrary restriction. – MT0 Jan 05 '16 at 10:05
  • I understand. OP posted same question yesterday http://stackoverflow.com/questions/34586914/oracle-sql-find-time-difference-both-time-in-string The only difference is that the previous question was specific to Oracle SQL, while this one is specific to Java. Also, OP wants to display in `HH24:MI:SS` format and not interval. – Lalit Kumar B Jan 05 '16 at 10:19
  • You can use java to do the string formatting or you can wrap the query result in `SUBSTR( ..., 12, 8 )` to get the `HH24:MI:SS` string format. – MT0 Jan 05 '16 at 10:33
  • @LalitKumarB This is a fair Answer, given that the Question specifically mentions Oracle database. If the original poster is not satisfied, they should make the effort to carefully craft their Question. – Basil Bourque Jan 05 '16 at 16:18
0

Convert the values to time and subtract them.

You can do this in Oracle or in java as you like most.

Here is an example on how to do it in oracle (it returns seconds between two dates)

select 
(TO_DATE('10:11:12', 'HH24:MI:SS') - TO_DATE('10:11:10', 'HH24:MI:SS')) *60*60*24 
from dual

Instead if you need the string in HH24:MI:SS format you can do the following, converting the seconds to a date and then formatting it to desired format:

select
TO_CHAR(TO_DATE(
  (TO_DATE('10:11:12','HH24:MI:SS') - TO_DATE('10:11:10','HH24:MI:SS')) *60*60*24
  , 'SSSSS')
, 'HH24:MI:SS')
from dual

Note that if 10:11:12 is not saved as a VARCHAR but as a DATE in oracle the query is simplified as follow:

select
TO_CHAR(TO_DATE(duration_1 - duration_2) *60*60*24, 'SSSSS'), 'HH24:MI:SS')
from dual
Davide Lorenzo MARINO
  • 26,420
  • 4
  • 39
  • 56
  • Not exactly. No need to multiply with `24*60*60` as it would convert it into seconds. OP wants to display it simply in `HH24:MI:SS` format. See http://stackoverflow.com/a/34587275/3989608 – Lalit Kumar B Jan 05 '16 at 09:27
0

This will give you the difference, note that they are milliseconds there, so divide the result by 1000 :

java.text.DateFormat format = new java.text.SimpleDateFormat("hh:mm:ss");

java.util.Date d2= df.parse(resultset.getString("Duration_2"));
java.util.Date d1= df.parse(resultset.getString("Duration_1"));

long difference = d1.getTime() - d2.getTime();
Arnaud
  • 17,229
  • 3
  • 31
  • 44