asked on

Oracle date time arithmetic: subtracting two dates to get minutes

Hi there,

I need to subtract two datetime fields in Oracle to get the difference in minutes.
for example:
begin_lab_time = 2011.12.12 08:10:00
end_lab_time = 2011.12.12 08:31:00
expected result from subtracting these two fields: 21 minutes

How would I go about getting the expected result in Oracle?

I tried this but I get 20 minutes as the result and not 21 minutes. I need to get 21 minutes as the result:
trunc((86400*(end_lab_time-begin_lab_time))/60)-60*(trunc(((86400*(end_lab_time-begin_lab_time))/60)/60))

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

W D

ASKER

ah. getting closer. I get 20.99999999999999999999999999999999999995 as the result, sdstuber. Is it best to use ROUND, then to get 21?

Sean Stuber

interesting.... I get different results based on whether I'm executing within pl/sql context or not.

SQL> SELECT (end_lab_time - begin_lab_time) * 1440
  2    FROM (SELECT TO_DATE('2011.12.12 08:10:00', 'yyyy.mm.dd hh24:mi:ss') begin_lab_time,
  3                 TO_DATE('2011.12.12 08:31:00', 'yyyy.mm.dd hh24:mi:ss') end_lab_time
  4            FROM DUAL);

(END_LAB_TIME-BEGIN_LAB_TIME)*1440
----------------------------------
                                21

SQL> DECLARE
  2      v_result         NUMBER;
  3      begin_lab_time   DATE := TO_DATE('2011.12.12 08:10:00', 'yyyy.mm.dd hh24:mi:ss');
  4      end_lab_time     DATE := TO_DATE('2011.12.12 08:31:00', 'yyyy.mm.dd hh24:mi:ss');
  5  BEGIN
  6      v_result  := (end_lab_time - begin_lab_time) * 1440;
  7      DBMS_OUTPUT.put_line(v_result);
  8
  9      SELECT (end_lab_time - begin_lab_time) * 1440 INTO v_result FROM DUAL;
 10
 11      DBMS_OUTPUT.put_line(v_result);
 12
 13      DBMS_OUTPUT.put_line((end_lab_time - begin_lab_time) * 1440);
 14  END;
 15  /
20.99999999999999999999999999999999999995
20.99999999999999999999999999999999999995
20.99999999999999999999999999999999999995

PL/SQL procedure successfully completed.

Open in new window

So, I guess...
yes, if you want to do your date math in pl/sql then you'll have to round it to get your results.

W D

ASKER

Re: So, I guess...
yes, if you want to do your date math in pl/sql then you'll have to round it to get your results.

ok, for my purposes, I will use ROUND; that works for my results.
Thanks very much for your help, it's much appreciated!