Oracle - Best Select Statement for Getting the Difference in Minutes Between Two Datetime Columns

Oracle - Best SELECT statement for getting the difference in minutes between two DateTime columns?

SELECT date1 - date2
  FROM some_table

returns a difference in days. Multiply by 24 to get a difference in hours and 24*60 to get minutes. So

SELECT (date1 - date2) * 24 * 60 difference_in_minutes
  FROM some_table

should be what you're looking for

Oracle : how to subtract two dates and get minutes of the result

When you subtract two dates in Oracle, you get the number of days between the two values. So you just have to multiply to get the result in minutes instead:

SELECT (date2 - date1) * 24 * 60 AS minutesBetween
FROM ...

Oracle; two dates, difference in minutes

Try this. YOu have to convert the systimestamp data type to a date type before you can do math on the two dates. Maybe there is a more elegant solution, but this seem to work.

SELECT ROUND (  (  SYSDATE
                 - TO_DATE (TO_CHAR (SYS_EXTRACT_UTC (SYSTIMESTAMP),
                                     'YYYY-MON-DD HH24:MI:SS'
                                    ),
                            'YYYY-MON-DD HH24:MI:SS'
                           )
                )
              * 1440,
              0
             )
  FROM DUAL

How can I get the difference in hours between two dates?

The error is because SYSDATE is already a date, there's no need to use TO_DATE() to convert it to a date.

If you don't convert it to a date:

select
    24 * (sysdate - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;

And if the formatting of the dates are wrong, you can possible use two steps like:

select
    24 * (to_date(to_char(sysdate, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;

Oracle query to fetch every minute between two timestamps

To get all the minutes between two datetime elements using Row Generator technique, you need to convert the difference between the dates into the number of minutes. Rest remains same in the CONNECT BY clause.

For example, to get all the minutes between 11/09/2015 11:00:00 and 11/09/2015 11:15:00:

SQL> WITH DATA AS
  2    (SELECT to_date('11/09/2015 11:00:00', 'DD/MM/YYYY HH24:MI:SS') date_start,
  3      to_date('11/09/2015 11:15:00', 'DD/MM/YYYY HH24:MI:SS') date_end
  4    FROM dual
  5    )
  6  SELECT TO_CHAR(date_start+(LEVEL -1)/(24*60), 'DD/MM/YYYY HH24:MI:SS') the_date
  7  FROM DATA
  8    CONNECT BY level <= (date_end - date_start)*(24*60) +1
  9  /

THE_DATE
-------------------
11/09/2015 11:00:00
11/09/2015 11:01:00
11/09/2015 11:02:00
11/09/2015 11:03:00
11/09/2015 11:04:00
11/09/2015 11:05:00
11/09/2015 11:06:00
11/09/2015 11:07:00
11/09/2015 11:08:00
11/09/2015 11:09:00
11/09/2015 11:10:00
11/09/2015 11:11:00
11/09/2015 11:12:00
11/09/2015 11:13:00
11/09/2015 11:14:00
11/09/2015 11:15:00

16 rows selected.

Above, CONNECT BY level <= (date_end - date_start)*(24*60) +1 means that we are generating rows as many as the number (date_end - date_start)*(24*60) +1. You get 16 rows, because it includes both the start and end window for the minutes.

---

Related Topics