Add day, hour, minute, second to a date value in Oracle

Add day, hour, minute, second to a date value in Oracle

Add day, hour, minute, second to a date value in Oracle : Multi-thousand tips to make your life easier.

Date arithmetic is very common in database applications. In Oracle, you can add, subtract, and compare DATE columns, but you cannot multiply or divide them. Oracle stores century, year, month, day, hour, minute, and second 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 the number of days. In other words, you need to convert hour, minute, and second to a 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 second along with date. As you can see from the above examples, date arithmetic in Oracle is simple and easy to use.