Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle date time arithmetic: subtracting two dates to get minutes

Posted on 2012-03-27
4
Medium Priority
?
834 Views
Last Modified: 2012-08-13
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))
0
Comment
Question by:wdelaney05
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37772326
(end_lab_time - begin_lab_time) * 1440
0
 

Author Comment

by:wdelaney05
ID: 37772436
ah. getting closer. I get 20.99999999999999999999999999999999999995 as the result, sdstuber. Is it best to use ROUND, then to get 21?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37772485
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.
0
 

Author Comment

by:wdelaney05
ID: 37772507
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!
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question