How to subtract dates and calculating time per day? PLSQL or Java

Question

good afternoon

My question is this: I need to calculate the time between two dates, but with the condition that for example if the first date is 01/20/2014 4:00:00 and the second is 01/21/2014 : 1:30:00 then the result should be:

01/20/2014 = 20 hours 01/21/2014 = 2.5 hours

and in the same way if the date range is wider for example a week etc..

I've managed to tell the difference plsql enter two date but not with this condition to know how much was the difference for each day.

Any idea how to approach this question? Whether in plsql or java CODES

Thank you in advance for your attention

What do you mean by "1:30 01/21/2014 : 00 "? Is the time the bit before or the bit afterwards? This question is *very* unclear. — Jon Skeet, Jan 22 '14 at 19:52
Please read [ask] and improve your question, it's hard to tell what you want to acheive and how to help you. — Jonathan Drapeau, Jan 22 '14 at 19:52
Sorry for my English, I just hope correct question is understood to changes — Juan Esteban Castaño Rincon, Jan 22 '14 at 19:56
[JodaTime](http://www.joda.org/joda-time/) for [example](http://stackoverflow.com/questions/20677541/date-range-in-date-range/20678485#20678485), [example](http://stackoverflow.com/questions/13328912/java-getting-time-interval/13329218#13329218) — MadProgrammer, Jan 22 '14 at 19:57

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

PL/SQL

Subtract the dates from one another, then use the NUMTODSINTERVAL function to convert the result to an interval:

SELECT NUMTODSINTERVAL(TO_DATE('01/21/2014 01:30:00', 'MM/DD/YYYY HH24:MI:SS') - 
                         TO_DATE('01/20/2014 04:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                       'DAY') AS TIMEDIFF
  FROM DUAL

This produces a result of

TIMEDIFF
+000000000 21:30:00

Share and enjoy.

score 0 · Answer 2 · answered Jan 22 '14 at 20:29

You could create your own function and then use it.

    CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1   NUMBER;
NDATE_2   NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);

BEGIN
  -- Get Julian date number from first date (DATE_1)
  NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

  -- Get Julian date number from second date (DATE_2)
  NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

  -- Get seconds since midnight from first date (DATE_1)
  NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

  -- Get seconds since midnight from second date (DATE_2)
  NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

  RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/

    --Difference in Hours: 
SELECT time_diff(To_Date('01/20/2014 04:00:00',
                            'MM/DD/YYYY HH24:MI:SS'),
                    To_Date('01/21/2014 01:30:00',
                            'MM/DD/YYYY HH24:MI:SS')) / 60 / 60
      FROM Dual

Source: http://psoug.org/reference/date_func.html

How to subtract dates and calculating time per day? PLSQL or Java

2 Answers2

PL/SQL