How to calculate time difference in sql

oracle

I have one table named test.
In that table I have a column named test_time of type DATE. I want to calculate the time difference between test_time and sysdate in minutes.

select 1440 * (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi')
- to_date('2009-07-07   19:30', 'YYYY-MM-DD hh24:mi')) diff_hours 
from dual;

this one giving time difference in minutes. But I tried this one to my table like

select 1440 * (to_date(test_time, 'YYYY-MM-DD hh24:mi')
- to_date(sysdate, 'YYYY-MM-DD hh24:mi')) diff_hours 
from test;

but I'm not able to calculate the time difference in minutes. Kindly help me to solve this problem.

Best Answer

DATEis the Oracle datetime data type for storing a datetime value with a precision of one second. The function TO_DATE converts a character type (VARCHAR2,CHAR,...) to the DATE type. The first string is the string to convert, the secons string tell the convert function which format is used to code the date in the first string. So the result of TO_DATE('2014-04-18 10:33', 'YYYY-MM-DD HH24:MI') is the DATE equivalent to '2014-04-18 10:33:00'. The seconds are set to 0.

The function SYSDATE returns a DATE value so it must not be converted further. The column test_time is of data type DATE, too, it also must not be converted further. To get the DATEdifference you have only to calculate test_time-SYSDATE This difference of two DATE values gives the number of days. Therefore you have to multiply the above value by 1440 (=24*60, the number of minutes per day). You end up with

select (test_time - sysdate)*24*60
  from test;

This value is negative if test_time is lies in the past. If you want an integer value then round the result

select round((test_time - sysdate)*24*60,0)
  from test;

Related Solutions

Simple query with date type requires explicit conversion to_date of date

Your belief that this should not be necessary is correct based on your assumptions. Normally this means the assumptions should be rechecked (unless there is data corruption or a bug). If you run the following you should get the same results from the first query as the second:

DROP TABLE t1;
CREATE TABLE t1 AS (
   SELECT to_date('01-FEB-2011','DD-MON-YYYY')+level myDateColumn 
   FROM dual CONNECT BY level <=120);

select * from t1 
where myDateColumn 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

select myDateColumn from t1 
where to_date(myDateColumn) 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

To check some of the assumptions, can you show the results of the following query?

SELECT myDateColumn, to_date(myDateColumn)
   , to_char(myDateColumn,'DD-MON-YY HH.MI.SS PM'), to_char(myDateColumn,'YYYY') 
FROM myTable
WHERE to_date(myDateColumn)
BETWEEN to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

The first three columns should all show identical information and the last should verify that the year is correct.

Oracle RAC Failover functionality clarification

Just from Oracle side there is nothing to worry about. You could even do this operation during normal business hours.

BUT! most Applications are not cluster ready. They cannot deal with this situation and will crash. All services are still available since they are migrated to the second node. But the session does not get migrated that's why the client will loose the connection. An application which supports RAC would then reopen the connection and just continue to work.

I would plan a downtime for this operation.

For the future you could bond two network ports for the public interface in failover mode. With that setup you could do the operation without downtime. Note that bonding in load balance mode does not work so well -- at least this is my experience.

Best Answer

Related Solutions

Simple query with date type requires explicit conversion to_date of date

Oracle RAC Failover functionality clarification

Related Question