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:
Who is Participating?
sdstuberConnect With a Mentor Commented:
(end_lab_time - begin_lab_time) * 1440
wdelaney05Author Commented:
ah. getting closer. I get 20.99999999999999999999999999999999999995 as the result, sdstuber. Is it best to use ROUND, then to get 21?
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', ' hh24:mi:ss') begin_lab_time,
  3                 TO_DATE('2011.12.12 08:31:00', ' hh24:mi:ss') end_lab_time
  4            FROM DUAL);


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

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.
wdelaney05Author Commented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.