Calculate difference between 2 date times in Oracle SQL

Question

I have a table as follows   Filename - varchar Creation Date - Date format dd mm yyyy hh24 mi ss Oldest cdr date - Date format dd mm yyyy hh24 mi ss   How can I calcuate the difference in hours minutes and seconds  and possibly days  between the two dates in Oracle SQL   Thanks

User · Answer

If you want something that looks a bit simpler, try this for finding events in a table which occurred in the past 1 minute:

With this entry you can fiddle with the decimal values till you get the minute value that you want. The value .0007 happens to be 1 minute as far as the sysdate significant digits are concerned. You can use multiples of that to get any other value that you want:

select (sysdate - (sysdate - .0007)) * 1440 from dual;

Result is 1 (minute)

Then it is a simple matter to check for

select * from my_table where (sysdate - transdate) < .00071;

User · Answer

declare strTime1 varchar2 50      02 08 2013 01 09 42 PM   strTime2 varchar2 50      02 08 2013 11 09 00 PM   v date1 date    to date strTime1  DD MM YYYY HH MI SS PM    v date2 date    to date strTime2  DD MM YYYY HH MI SS PM    difrence In Hours number  difrence In minutes number  difrence In seconds number  begin     difrence In Hours       v date2 - v date1    24      difrence In minutes    difrence In Hours   60      difrence In seconds    difrence In minutes   60       dbms output put line strTime1               dbms output put line strTime2       dbms output put line                 dbms output put line  difrence In Hours         difrence In Hours       dbms output put line  difrence In minutes       difrence In minutes       dbms output put line  difrence In seconds       difrence In seconds           end     Hope this helps

User · Answer

Single query that will return time difference of two timestamp columns   select INS TS  MAIL SENT TS  extract  hour from  INS TS - MAIL SENT TS    timeDiff  from MAIL NOTIFICATIONS

User · Answer

select round   tbl Todate - tbl fromDate    24   60   60   from table tbl

User · Answer

select to char actual start date  DD-MON-YYYY hh24 mi ss   start time  to char actual completion date  DD-MON-YYYY hh24 mi ss   end time  floor  actual completion date-actual start date  24 60        round mod  actual completion date-actual start date  24 60 60 60   diff time from fnd concurrent requests  order by request id desc

User · Answer

TO DATE  P comapre date 1   dd-mm-yyyy hh24 mi   - TO DATE  P comapre date 2   dd-mm-yyyy hh24 mi    60 60 24 sum seconds            TO DATE  P comapre date 1   dd-mm-yyyy hh24 mi   - TO DATE  P comapre date 2   dd-mm-yyyy hh24 mi    60 24 sum minutes            TO DATE  P comapre date 1   dd-mm-yyyy hh24 mi   - TO DATE  P comapre date 2   dd-mm-yyyy hh24 mi    24 sum hours            TO DATE  P comapre date 1   dd-mm-yyyy hh24 mi   - TO DATE  P comapre date 2   dd-mm-yyyy hh24 mi    sum days

User · Answer

Calculate age from HIREDATE to system date of your computer  SELECT HIREDATE              SYSDATE               TRUNC MONTHS BETWEEN SYSDATE HIREDATE  12      YEARS     TRUNC  MONTHS BETWEEN SYSDATE HIREDATE  - TRUNC MONTHS BETWEEN SYSDATE HIREDATE  12  12      MONTHS  AS  AGE     FROM EMP

User · Answer

select      extract  day from diff   Days       extract  hour from diff   Hours       extract  minute from diff   Minutes  from           select  CAST creationdate as timestamp  - CAST oldcreationdate as timestamp   diff            from  TableName             This will give you three columns as Days  Hours and Minutes

User · Answer

select  floor   DATE2-DATE1  24 60 60  3600            floor    DATE2-DATE1  24 60 60  -floor   DATE2-DATE1  24 60 60  3600  3600  60           as time difference from TABLE1

User · Answer

You could use to timestamp function to convert the dates to timestamps and perform a substract operation   Something like   SELECT  TO TIMESTAMP   13 10 1990 00 00 00   DD MM YYYY HH24 MI SS    -  TO TIMESTAMP   01 01 1990 00 10 00   DD MM YYYY HH24 MI SS   FROM DUAL

User · Answer

sql  select bsp bp user name status  to char ins date  dd mm yyyy hh12 mi ss AM    to char pickup date  dd mm yyyy hh12 mi ss AM    trunc  pickup date-ins date  24 60 60 2  message status message  from valid bsp req where id  gt     id

User · Answer

Here s another option   with tbl demo AS      SELECT TO DATE  11 26 2013 13 18 50    MM DD YYYY HH24 MI SS   dt1      TO DATE  11 28 2013 21 59 12    MM DD YYYY HH24 MI SS   dt2       FROM dual  SELECT dt1        dt2        round dt2 - dt1 2  diff days        round dt2 - dt1 2  24 diff hrs        numtodsinterval  dt2 - dt1   day   diff dd hh mm ss   from tbl demo

User · Answer

select days        time from   SELECT to number  to char to date  1   J          CLOSED DATE - CREATED DATE    J   - 1   days     to char to date  00 00 00   HH24 MI SS            CLOSED DATE - CREATED DATE    HH24 MI SS   time  FROM  request  where REQUEST ID 158761088

User · Answer

You may also try this   select to char to date  1970-01-01 00 00 00    yyyy-mm-dd hh24 mi ss    end date - start date   hh24 mi ss          as run time from some table    It displays time in more human readable form  like  00 01 34  If you need also days you may simply add DD to last formatting string

User · Answer

If you select two dates from  your table  and want too see the result as a single column output  eg   days - hh mm ss    you could use something like this  First you could calculate the interval between these two dates and after that export all the data you need from that interval            select     extract  day from numtodsinterval  second date                                                    - add months  created date                                                                   floor  months between  second date created date                                                         day                      days -                   extract  hour from numtodsinterval  second date                                                     - add months  created date                                                                    floor  months between  second date created date                                                          day                                        extract  minute from numtodsinterval  second date                                                       - add months  created date                                                                      floor  months between  second date  created date                                                            day                                        extract  second from numtodsinterval  second date                                                       - add months  created date                                                                      floor  months between  second date  created date                                                            day         from    your table   And that should give you result like this           0 days - 1 14 55

User · Answer

If You want get date defer from using table and column   SELECT TO DATE  TO CHAR COLUMN NAME 1   YYYY-MM-DD     YYYY-MM-DD   -         TO DATE TO CHAR COLUMN NAME 2   YYYY-MM-DD      YYYY-MM-DD    AS DATEDIFF        FROM TABLE NAME

User · Answer

You can substract dates in Oracle  This will give you the difference in days  Multiply by 24 to get hours  and so on   SQL gt  select oldest - creation from my table    If your date is stored as character data  you have to convert it to a date type first   SQL gt  select 24    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   DIFF HOURS ----------        2 5     Note   This answer applies to dates represented by the Oracle data type DATE  Oracle also has a data type TIMESTAMP  which can also represent a date  with time   If you subtract TIMESTAMP values  you get an INTERVAL  to extract numeric values  use the EXTRACT function

User · Answer

In oracle 11g   SELECT end date - start date AS day diff FROM tablexxx suppose the starT date end date is define in the tablexxx

User · Answer

This will count time between to dates   SELECT    TO CHAR  TRUNC  ROUND   sysdate 1  - sysdate  24 2   60  999999         TO CHAR     sysdate 1 -sysdate  24 - TRUNC ROUND   sysdate 1  - sysdate  24 2    100 60  100   09    60 FROM dual

User · Answer

To get result in seconds   select  END DT - START DT  60 60 24 from MY TABLE    Check  https   community oracle com thread 2145099 tstart 0  1

[oracle] Calculate difference between 2 date / times in Oracle SQL

Examples related to oracle

Examples related to date-arithmetic