- 浏览: 284627 次
- 性别:
- 来自: 上海
文章分类
最新评论
-
SpringJava:
摘过来的
小心使用ArrayList和LinkedList -
jingjing0907:
我要成为第一个赞的人!呵呵,
小心使用ArrayList和LinkedList -
SpringJava:
cilendeng 写道应该用ConcurrentHashMa ...
实现单用户登陆session先进先出(session踢出) -
lingxiajiudu:
不错,完美解决我了的问题,赞一个。
子窗体给父窗体传值 javascript opener -
cilendeng:
应该用ConcurrentHashMap
实现单用户登陆session先进先出(session踢出)
How To Add Day, Hour, Minute, Second to a Date Value in Oracle
- 博客分类:
- 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:
-
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 -
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 -
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 -
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"><!-- google_ad_client = "pub-8854149860478140"; google_ad_host = "pub-7579606133989673"; google_ad_type = "text_image"; google_ad_channel = "7297636634"; google_ad_width = 300; google_ad_height = 250; google_ad_format = "300x250_as"; google_color_border = "FFFFFF"; google_color_bg = "FFFFFF"; google_color_link = "0000ff"; google_color_url = "336699"; google_color_text = "000000"; //--></script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script> -
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 -
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 -
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 -
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
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.
- Backup your original table. This is the most important step!
- Make sure your backup is good and you can restore original table if you need to.
-
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"><!-- google_ad_client = "pub-8854149860478140"; google_ad_host = "pub-7579606133989673"; google_ad_type = "text_image"; google_ad_channel = "7297636634"; google_ad_width = 300; google_ad_height = 250; google_ad_format = "300x250_as"; google_color_border = "FFFFFF"; google_color_bg = "FFFFFF"; google_color_link = "0000ff"; google_color_url = "336699"; google_color_text = "000000"; //--></script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script> - Verify your results and commit your work.
Now let's look at a second method.
- Backup your original table. This is still the most important step!
- Make sure your backup is good and you can restore original table if you need to.
- Create a temporary table with unique records using DISTINCT clause.
- Delete all records from original table.
- Insert all records from temporary table into original table.
- 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.
发表评论
-
Oracle 查询并删除重复记录的SQL语句
2010-05-04 10:54 1184查询及删除重复记录的SQL语句1、查找表中多余的重复记录,重复 ... -
delete和truncate的区别
2010-04-29 20:09 1215TRUNCATE和DELETE有以下几点区别 1、 ... -
Oracle常见SQL分页实现方案
2010-04-26 18:52 1165在Oracle中,用SQL来实现分页有很多种实现方式,但有些语 ... -
如何高效删除Oracle数据库中的重复数据
2010-04-26 18:37 1353重复数据删除技术可以 ... -
ORACLE 常用函数
2010-01-23 20:41 9591.INITCAP 返回字符串并将 ... -
用select 语句中的START WITH...CONNECT BY PRIOR子句实现递归查询
2010-01-18 16:47 1364Finally, the next example adds ... -
Oracle正则表达式
2009-12-31 13:20 6130oracle的正则表达式(regula ... -
oracle 忘记sys 密码后,更改方法
2009-12-17 15:47 983D:\oracle\ora92\bin>sqlplus ... -
Oracle的数据类型转换 to_char(date)
2009-12-03 12:42 4138Oracle的数据类型转换 ... -
用java调用oracle存储过程总结
2009-08-06 09:22 9601、什么是存储过程。存 ... -
Oracle提高查询效率
2009-04-24 11:43 930(1) 选择最有效率的 ... -
oracle基本操作
2009-04-24 11:32 986在SQLPLUS下,实现中-英 ...
相关推荐
This demonstrates how to add radio buttons to your menus
In just one hour a day, you’ll learn the skills you need to design, create, and maintain a professional-looking website. No previous experience required. By following each short, one-hour lesson in ...
Oracle Database 2 Day DBA is a database administration quick start guide that teaches you how to perform day-to-day database administrative tasks. The goal of this guide is to help you understand the ...
source insight is the best code viewer. but it can't auto add ...the details how to add is saved in the file "how to add comment in source insight.txt". i tried it and it works. so, share it with you.
How to Write a Good Scientific Paper by Chris A. Mack is a valuable resource for all students and faculty in science and engineering. SPIE is pleased to make it available here at no charge as a PDF ...
How to Design Programs, Second Edition
使用Haskell和Monad技术实现一个解释器。又学Monad也学解释器。
How to use epoll A complete example in C How to use epoll A complete example in C How to use epoll A complete example in C How to use epoll A complete example in C
This demonstrates how to add images to a menu.
How To Add Nand Flash Driver (CN) How To Add Nand Flash Driver (CN) How To Add Nand Flash Driver (CN)
MBA CASE, HOW TO MAKE YOUR CASE IN 30 SECOND OR LESS
Sams Teach Yourself C++ in One Hour a Day ' In just one hour a day, you'll have all the skills you need to begin programming in C++. With this complete tutorial, you'll quickly master the basics and ...
How and why I came to use Python In 1999, the College Board's Advanced Placement (AP) Computer Science exam was given in C++ for the ˉrst time. As in many high schools throughout the country, the ...
How to make mistakes in Python
This book would not exist without the lessons I learned from my parents and ... We are committed to making a difference, and the dedication to produce How to Speak Tech is in service of these efforts.
From proposal to examination, producing a dissertation or thesis is a challenge. Grounded in decades ... How to Write a Better Thesis presents a cohesive approach to research that will help you succeed.
How to Read a Book, originally published in 1940, has become a rare phenomenon, a living classic. It is the best and most successful guide to reading comprehension for the general reader. And now it ...
How to write a Routine in DTP
George Polya revealed how the mathematical method of demonstrating a proof or finding an unknown can be of help in attacking any problem that can be "reasoned" out - from building a bridge to winning...
How not to Program in C++A 111 Broken Programs and 3 Working Ones, or Why Does 2+2=5986