Operations With Time Types in a Database

Addition and Subtraction of Days, Months, and Years

In Oracle, when dealing with date types, you can directly add or subtract days. However, when it comes to manipulating months, you would use the add_months function:

SQL> SELECT hiredate AS Hire Date,
          hiredate - 5 AS Minus 5 Days,
          hiredate + 5 AS Plus 5 Days,
          add_months(hiredate, -5) AS Minus 5 Months,
          add_months(hiredate, 5) AS Plus 5 Months,
          add_months(hiredate, -5 * 12) AS Minus 5 Years,
          add_months(hiredate, 5 * 12) AS Plus 5 Years
     FROM emp
     WHERE ROWNUM <= 1; 

Addition and Subtraction of Hours, Minutes, and Seconds

SQL> SELECT hiredate AS Hire Date,
           hiredate - 5 / 24 / 60 / 60 AS Minus 5 Seconds,
           hiredate + 5 / 24 / 60 / 60 AS Plus 5 Seconds,
           hiredate - 5 / 24 / 60 AS Minus 5 Minutes,
           hiredate + 5 / 24 / 60 AS Plus 5 Minutes,
           hiredate - 5 / 24 AS Minus 5 Hours,
           hiredate + 5 / 24 AS Plus 5 Hours
      FROM emp
     WHERE ROWNUM <= 1;

Time Intervals in Hours, Minutes, and Seconds

SQL> SELECT Interval_Days,
           Interval_Days * 24 AS Interval_Hours,
           Interval_Days * 24 * 60 AS Interval_Minutes,
           Interval_Days * 24 * 60 * 60 AS Interval_Seconds
    FROM( SELECT MAX(hiredate) - MIN(hiredate) AS Interval_Days
    FROM emp
    WHERE ename IN ('WARD','ALLEN'))X;

Time Intervals in Days, Months, and Years

SQL> SELECT max_hd - min_hd AS Days_Interval,
           months_between(max_hd, min_hd) AS Months_Interval,
           months_between(max_hd, min_hd) / 12 AS Years_Interval
      FROM (SELECT min(hiredate) as min_hd, MAX(hiredate) as max_hd FROM emp) x;