How To Add Day, Hour, Minute, Second to a Date Value in Oracle

Date arithmetic is very common in database application. In Oracle, you can add, subtract and compare DATE columns, but you can not multiply or divide it. Oracle stores century, year, month, day, hour, min and seconds as part of the DATE column.

Now let’s take a look at how to add day/hour/minute/second to a date value. Oracle expects a number constant in date arithmetic as number of days. In other words, you need to convert hour, minute and seconds to fraction of a day and then you can add or subtract that value from a date value. Here are some examples:

1. Add a day.
   select to_date('02-22-2008 10:30:30','mm-dd-yyyy hh24:mi:ss') today,
   to_date('02-22-2008 10:30:30','mm-dd-yyyy hh24:mi:ss')+1 next_day
   from dual;
   TODAY NEXT_DAY
   ------------------------- -------------------------
   02-22-08 10:30:30 02-23-08 10:30:30
2. Add an hour.
   select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
   to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss')+ 1/24 next_hour
   from dual;
   TODAY NEXT_HOUR
   ------------------------ ------------------------
   02-22-08 10:30:30 02-22-08 11:30:30
3. Add a minute.
   select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
   to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss')+ 1/(24*60) next_min
   from dual;
   TODAY NEXT_MIN
   ------------------------ ------------------------
   02-22-08 10:30:30 02-22-08 10:31:30
4. Add a second.
   select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
   to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss')+ 1/(24*60*60) next_sec
   from dual;
   TODAY NEXT_SEC
   ------------------------ ------------------------
   02-22-08 10:30:30 02-22-08 10:30:31


5. Subtract a day.
   select to_date('02-22-2008 10:30:30','mm-dd-yyyy hh24:mi:ss') today,
   to_date('02-22-2008 10:30:30','mm-dd-yyyy hh24:mi:ss') - 1 prev_day
   from dual;
   TODAY PREV_DAY
   ------------------------- -------------------------
   02-22-08 10:30:30 02-21-08 10:30:30
6. Subtract an hour.
   select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
   to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') - 1/24 prev_hour
   from dual;
   TODAY PREV_HOUR
   ------------------------ ------------------------
   02-22-08 10:30:30 02-22-08 09:30:30
7. Subtract a minute.
   select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
   to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') - 1/(24*60) prev_min
   from dual;
   TODAY PREV_MIN
   ------------------------ ------------------------
   02-22-08 10:30:30 02-22-08 10:29:30
8. Subtract a second.
   select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
   to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') - 1/(24*60*60) prev_sec
   from dual;
   TODAY PREV_SEC
   ------------------------ ------------------------
   02-22-08 10:30:30 02-22-08 10:30:29

Date value in Oracle has two components, date and time. Oracle stores hour, minute and seconds along with date. As you can see from the above examples, date arithmetic in Oracle is simple and easy to use.