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

 

<script type="text/javascript">&lt;!-- google_ad_client = &quot;pub-8854149860478140&quot;; google_ad_host = &quot;pub-7579606133989673&quot;; google_ad_type = &quot;text_image&quot;; google_ad_channel = &quot;5006302010&quot;; google_ad_width = 300; google_ad_height = 250; google_ad_format = &quot;300x250_as&quot;; google_color_border = &quot;FFFFFF&quot;; google_color_bg = &quot;FFFFFF&quot;; google_color_link = &quot;0000ff&quot;; google_color_url = &quot;336699&quot;; google_color_text = &quot;000000&quot;; //--&gt;</script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>

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
   <script type="text/javascript">&lt;!-- google_ad_client = &quot;pub-8854149860478140&quot;; google_ad_host = &quot;pub-7579606133989673&quot;; google_ad_type = &quot;text_image&quot;; google_ad_channel = &quot;7297636634&quot;; google_ad_width = 300; google_ad_height = 250; google_ad_format = &quot;300x250_as&quot;; google_color_border = &quot;FFFFFF&quot;; google_color_bg = &quot;FFFFFF&quot;; google_color_link = &quot;0000ff&quot;; google_color_url = &quot;336699&quot;; google_color_text = &quot;000000&quot;; //--&gt;</script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>
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.

 

==============================================

How To Get Rid of Duplicate Rows in an Oracle SQL Table

<script type="text/javascript">&lt;!-- google_ad_client = &quot;pub-8854149860478140&quot;; google_ad_host = &quot;pub-7579606133989673&quot;; google_ad_type = &quot;text_image&quot;; google_ad_channel = &quot;5006302010&quot;; google_ad_width = 300; google_ad_height = 250; google_ad_format = &quot;300x250_as&quot;; google_color_border = &quot;FFFFFF&quot;; google_color_bg = &quot;FFFFFF&quot;; google_color_link = &quot;0000ff&quot;; google_color_url = &quot;336699&quot;; google_color_text = &quot;000000&quot;; //--&gt;</script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>

If you are using Oracle Database, a common problem is duplicate rows in tables. Usually this happens when unique constraints are removed during loading. Whatever may be the reason, you can remove duplicate records. Duplicate records have identical values for all columns (OR columns that are part of unique key).

There is more than one way to delete duplicate records. Let's assume Table_A has the following data:

Col1 Col2
------ -------
101 201
102 202
103 203
102 202
104 204
101 201

Let's look at the first method.

1. Backup your original table. This is the most important step!
2. Make sure your backup is good and you can restore original table if you need to.
3. Here is the code to delete duplicate records:
   
   DELETE FROM Table_A a
   WHERE ROWID != ( SELECT MAX(ROWID)
   FROM Table_A b
   WHERE b.col1 = a.col1
   AND b.col2 = a.col2 );

   Oracle has a pseudo column called "ROWID", which is unique for each row in a table. So we can use that to delete duplicate records. The above code will find only one ROWID for non-duplicate records. For duplicate records, it'll delete a record with lower ROWID. If there are more than two columns that make up unique key for a record, then you should include all those columns in WHERE condition. There are two duplicate records in the example; it does not matter which record we delete since all columns have the same values.

   <script type="text/javascript">&lt;!-- google_ad_client = &quot;pub-8854149860478140&quot;; google_ad_host = &quot;pub-7579606133989673&quot;; google_ad_type = &quot;text_image&quot;; google_ad_channel = &quot;7297636634&quot;; google_ad_width = 300; google_ad_height = 250; google_ad_format = &quot;300x250_as&quot;; google_color_border = &quot;FFFFFF&quot;; google_color_bg = &quot;FFFFFF&quot;; google_color_link = &quot;0000ff&quot;; google_color_url = &quot;336699&quot;; google_color_text = &quot;000000&quot;; //--&gt;</script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>
4. Verify your results and commit your work.

Now let's look at a second method.

1. Backup your original table. This is still the most important step!
2. Make sure your backup is good and you can restore original table if you need to.
3. Create a temporary table with unique records using DISTINCT clause.
4. Delete all records from original table.
5. Insert all records from temporary table into original table.
6. Verify your results and commit your work.

Depending on your situation, you can choose one of the two methods for faster results. Duplicate records can be prevented using Unique Indexes. Unique Indexes guarantees that columns within the index have no duplicate values.