- 浏览: 166284 次
- 性别:
- 来自: 武汉
文章分类
- 全部博客 (95)
- oracle (7)
- java 基础 (10)
- web层 (3)
- 框架技术 (0)
- eclipse技巧 (2)
- 重构 (2)
- 架构 (1)
- 缓存 (11)
- 分布式多线程编程 (5)
- jquery (8)
- ibatis (3)
- spring API (1)
- maven (1)
- spring (4)
- js (12)
- 正则表达式 (1)
- Velocit (1)
- 杂七杂八 (3)
- XSS跨域攻击 (1)
- appfuse (2)
- Simple-Spring-Memcached (3)
- spring batch (11)
- spring integration (2)
- Teiid 8.0 (0)
- Linux 命令 (1)
最新评论
-
luxianping:
怎么根据命名空间来删除缓存? 比如说用户对应的菜单,当有用户 ...
使用SSM注解做缓存操作 -
vemacitri_b:
mowengaobo 写道你可以这样理解key。为什么不单独放 ...
使用SSM注解做缓存操作 -
mowengaobo:
你可以这样理解key。为什么不单独放个参数出来如 @Rea ...
使用SSM注解做缓存操作 -
夜神月:
目前我在dao曾有如下的注解配置:@ReadThroughSi ...
使用SSM注解做缓存操作
Date / Time Arithmetic with Oracle 9/10
Overview
If you store date and time information in Oracle, you have two different options for the column's datatype - DATE and TIMESTAMP.
DATE is the datatype that we are all familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype.
In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date:
SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "hiredate"
FROM emp;hiredate
-------------------
17.12.1980:00:00:00
20.02.1981:00:00:00You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days . For example:
- SYSDATE + 1 is tomorrow
- SYSDATE - 7 is one week ago
- SYSDATE + (10/1440) is ten minutes from now.
Subtracting the HIREDATE column of the EMP table from SYSDATE returns the number of days since each employee was hired.
SELECT '03.12.2004:10:34:24' "Now", TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate", TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS') - hiredate "Hired since [Days]" FROM emp; Now Hiredate Hired since [Days] ------------------- ------------------- ------------------ 03.12.2004:10:34:24 17.12.1980:00:00:00 8752.44056You cannot multiply or divide DATE values. Oracle provides functions for many common date operations. For example, the ADD_MONTHS function lets you add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates.
Subtraction between Dates
The trouble people get into when using the DATE datatype is doing arithmetic on the column in order to figure out the number of years, weeks, days, hours, and seconds between two dates. What needs to be realized when doing the calculation is that when you do subtraction between dates, you get a number that represents the number of days . You should then multiply that number by the number of seconds in a day (86400) before you continue with calculations to determine the interval with which you are concerned.
DEFINE Today = TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",
trunc((86400*(&Today-hiredate))/60)-60*(trunc(((86400*(&&Today-hiredate))/60)/60)) "Min",
trunc(((86400*(&Today-hiredate))/60)/60)-24*(trunc((((86400*(&&Today-hiredate))/60)/60)/24)) "Hrs",
trunc((((86400*(&Today-hiredate))/60)/60)/24) "Days"
FROM emp;
Hiredate Today Sec Min Hrs Days
------------------- ------------------- --- --- --- -----
17.12.1980:00:00:00 03.12.2004:10:34:24 24 34 10 8752Check out the above query for a possible solution on how to extract the individual time intervals for a subtraction of two dates. The fractions could be reduced but we wanted to show all the numbers to emphasize the calculation.
If you want a solution which breaks the days in years and month you can use the following query. We will use a leap year date, 01/01/2000 for example, for temporary purposes. This date will provide accurate calculation for most cases.
DEFINE DateDay = 8752.44056
SELECT
TO_NUMBER(SUBSTR(A,1,4)) - 2000 years,
TO_NUMBER(SUBSTR(A,6,2)) - 01 months,
TO_NUMBER(SUBSTR(A,9,2)) - 01 days,
SUBSTR(A,12,2) hours,
SUBSTR(A,15,2) minutes,
SUBSTR(A,18,2) seconds
FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD')
+ &DateDay,'YYYY MM DD HH24:MI:SS') A
FROM DUAL);
YEARS MONTHS DAYS HO MI SE
---------- ---------- ---------- -- -- --
23 11 17 10 34 24
The new TIMESTAMP datatype
One of the main problems with the DATE datatype was its' inability to be granular enough to determine which event might have happened first in relation to another event. Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds.
Convert DATE datatype to TIMESTAMP datatype
If you want to convert a DATE datatype to a TIMESTAMP datatype format, just use the CAST function. As you can see, there is a fractional seconds part of '.000000' on the end of this conversion. This is only because when converting from the DATE datatype that does not have the fractional seconds it defaults to zeros and the display is defaulted to the default timestamp format (NLS_TIMESTAMP_FORMAT). If you are moving a DATE datatype column from one table to a TIMESTAMP datatype column of another table, all you need to do is a INSERT SELECT FROM and Oracle will do the conversion for you.
CREATE TABLE date_table (
date1 DATE,
time1 TIMESTAMP,
time2 TIMESTAMP
);
INSERT INTO date_table (date1, time1, time2)
VALUES (SYSDATE,
TO_TIMESTAMP ('17.12.1980:00:00:00 ','DD.MM.YYYY:HH24:MI:SS') ,
TO_TIMESTAMP ('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
);
COMMIT;
SELECT CAST(date1 AS TIMESTAMP) "Date" FROM date_table;
Date
---------------------------------------------------------------------------
03-DEC-04 11.36.45.000000 AM
The TO_TIMESTAMP function
The TO_TIMESTAMP function converts a string to a timestamp. The syntax for the to_timestamp function is:
TO_TIMESTAMP ( string , [ format_mask ] [ 'nlsparam' ] )
string is the string that will be converted to a timestamp.
format_mask is optional. This is the format that will be used to convert string to a timestamp.The following is a list of options for the format_mask parameter These parameters can be used in many combinations.
Parameter
Explanation
YYYY
4-digit year
MM
Month (01-12; JAN = 01).
MON
Abbreviated name of month.
MONTH
Name of month, padded with blanks to length of 9 characters.
DD
Day of month (1-31).
HH
Hour of day (1-12).
HH12
Hour of day (1-12).
HH24
Hour of day (0-23).
MI
Minute (0-59).
SS
Second (0-59).
Formatting of the TIMESTAMP datatype
Formatting of the new TIMESTAMP datatype is the same as formatting the DATE datatype. Beware while the TO_CHAR function works with both datatypes, the TRUNC function will not work with a datatype of TIMESTAMP . This is a clear indication that the use of TIMESTAMP datatype should explicitly be used for date and times where a difference in time is of utmost importance, such that Oracle won't even let you compare like values. If you wanted to show the fractional seconds within a TIMESTAMP datatype, look at the 'FF3' to only showing 3 place holders for the fractional seconds.
Formatting of the TIMESTAMP datatype:
SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date"
FROM date_table;
Date
-------------------
12/17/1980 00:00:00Formatting of the TIMESTAMP datatype with fractional seconds:
SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date"
FROM date_table;
Date
-----------------------------
12/17/1980 00:00:00:000
Subtraction of two TIMESTAMP datatypes
Calculating the time difference between two TIMESTAMP datatypes is much easier than the old DATE datatype . Look at what happens when you just do the same substraction as in the above queries:
SELECT SUBSTR(time1,1,30) "Time1",
SUBSTR(time2,1,30) "Time2",
SUBSTR((time2-time1),1,30) "Time1 - Time2"
FROM date_table;
Time1 Time2 Time1 - Time2
------------------------------ ------------------------------ ---------------------------
17-DEC-80 12.00.00.000000 AM 03-DEC-04 10.34.24.000000 AM +000008752 10:34:24.000000As you can see, the results are much easier to recognize, 8752 days, 10 hours, 34 minutes, and 24 seconds. This means no more worries about how many seconds in a day and all those cumbersome calculations. And therefore the calculations for getting the weeks, days, hours, minutes, and seconds becomes a matter of picking out the number by using the SUBSTR function as can be seen next:
SELECT SUBSTR(time1,1,30) "Time1",
SUBSTR(time2,1,30) "Time2",
SUBSTR((time2-time1), INSTR((time2-time1),' ')+7,2) "SS",
SUBSTR((time2-time1), INSTR((time2-time1),' ')+4,2) "MI",
SUBSTR((time2-time1), INSTR((time2-time1),' ')+1,2) "HH",
TRUNC(TO_NUMBER(SUBSTR((time2-time1),1, INSTR(time2-time1,' ')))) "Days"
FROM date_table;
Time1 Time2 SS MI HH Days
------------------------------ ------------------------------ -- -- -- ----------
17-DEC-80 12.00.00.000000 AM 03-DEC-04 10.34.24.000000 AM 24 34 10 8752
NEXT_DAY and LAST_DAY functions
The NEXT_DAY and LAST_DAY functions can be used to calculate for example «the last Saturday in any given month». You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one.
NEXT_DAY (date, char)
NEXT_DAY returns the date of the first weekday named by char that is later than date . The return type is always DATE, regardless of the datatype of date . The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.
Example
Return the date of the next Monday after now:
SELECT TO_CHAR(NEXT_DAY(sysdate,'MON'),'DD.MM.YYYY') "Next Monday from now"
FROM DUAL;
Next Monday
-----------
06.12.2004
LAST_DAY(date)
LAST_DAY returns the date of the last day of the month that contains date . The return type is always DATE, regardless of the datatype of date.
Example
The following statement determines how many days are left in the current month:
SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;
SYSDATE Last Days Left
--------- --------- ----------
03-DEC-04 31-DEC-04 28Get the last date of a month:
SELECT LAST_DAY (TO_DATE ('02','MM')) FROM dual;
LAST_DAY
---------
29-FEB-04
Return the last Saturday of each month for a given year
You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one.
DEFINE my_month = 12;
SELECT TO_CHAR (
NEXT_DAY (
LAST_DAY (TO_DATE (&my_month,'MM' )) - 7,
TO_CHAR (TO_DATE ('29-01-1927', 'DD-MM-YYYY' ),'DAY')
),'DD.MM.YYYY') "Last Saturday in December 2004"
FROM dual;
Last Saturday in December 2004
------------------------------
25.12.2004Return the last Saturdays for the current year.
SELECT TO_CHAR (
NEXT_DAY (
LAST_DAY (
ADD_MONTHS (TRUNC(SYSDATE,'Y'),ROWNUM-1))-7,
TO_CHAR (TO_DATE('29-01-1927', 'DD-MM-YYYY'),'DAY')
), 'DD.MM.YYYY') "Last Saturdays in 2004"
FROM ALL_OBJECTS
WHERE ROWNUM <= 12;
Last Saturdays in 2004
----------------------
31.01.2004
28.02.2004
27.03.2004
24.04.2004
29.05.2004
26.06.2004
31.07.2004
28.08.2004
25.09.2004
30.10.2004
27.11.2004
25.12.2004The "29-01-1927" is just a random date that we knew was a Saturday—any Saturday would do. This is done instead of using "SAT" in the query for international reasons, because in languages other than English, "SAT" isn't Saturday. This query should work in any language out there.
发表评论
-
sql模糊查询
2012-02-01 15:27 1867一般模糊语句如下: S ... -
ORA-00911: invalid character的解决办法
2012-01-16 10:31 1034ORA-00911: invalid character的解决 ... -
oracle里long类型的总结
2011-01-26 16:05 11751、LONG 数据类型中存储的是可变长字符串,最大长度限制 ... -
Dropping Columns
2010-12-07 15:12 901Home » Articles » 8i » ... -
关于update set from,第一次碰到,汗!
2010-12-07 15:02 1131关于update set from,第 ... -
plsql 意外 drop table 如何找回。
2010-11-15 17:24 2141恢复oracle中用pl sql误删除drop掉的表 ...
相关推荐
Oracle 常用函数汇总大全 Oracle 是一个功能强大且广泛使用的关系数据库管理系统,提供了许多有用的函数来帮助开发者快速实现各种数据操作。下面是 Oracle 中一些常用的函数的汇总。 运算符 Oracle 中有多种...
Oracle 日期和时间处理汇总 Oracle 日期和时间处理是数据库管理系统中非常重要的一部分,本文汇总了 oracle 中日期和时间处理的各种函数和使用方法,并提供了实例供参考。 一、日期和字符转换函数 Oracle 中...
ORACLE SQL:经典查询练手系列文章汇总 通过近一个月的努力,《经典查询练手系列》也快告一段落,但并不代表结束,以后还会增加!我很荣幸本系列每一篇文章都上了评论或推荐头条,这离不开大家的支持,这也促蹴了...
Oracle RAC的核心在于其集群技术,它允许多个实例在同一时间访问和操作同一个物理数据库。通过集群软件(如Oracle Clusterware)和RAC组件,各节点间的数据一致性得到保证。当客户端连接到任一服务器的监听器时,...
ORACLE 中日期和时间函数汇总 Oracle 中的日期和时间函数是数据库管理系统中非常重要的一部分,掌握这些函数可以帮助开发者和 DBA 更好地处理日期和时间相关的操作。 日期和字符转换函数 Oracle 中提供了两个...
Oracle数据库面试题汇总 本资源摘要信息涵盖了Oracle数据库面试中的多个知识点,包括字符串操作函数、事务概念、系统时间查询、触发器的作用、数字函数、关系数据库系统与文件数据库系统的区别、触发器和存储过程的...
### Oracle语句优化规则汇总 #### 一、选用适合的Oracle优化器 Oracle数据库提供了三种不同的优化器选项:基于规则的优化器(RULE)、基于成本的优化器(COST)和选择性优化器(CHOOSE)。这些优化器的选择对查询...
### Oracle巡检语句汇总详解 #### 一、检查数据库基本状况 在Oracle数据库的日常运维工作中,确保数据库的基本运行状况良好是非常重要的第一步。这部分主要包括以下几个方面: ##### 1.1 检查Oracle实例状态 ```...
本资源摘要信息将涵盖 Oracle 数据库面试题目汇总的关键知识点,涵盖字符串操作函数、事务概念、查询系统时间、触发器的作用、数字函数、关系数据库系统与文件数据库系统的区别、触发器和存储过程的概念等方面。...
本资源“oracle数据仓库分析函数汇总”聚焦于Oracle数据库中的分析函数,尤其是与`OVER()`子句相关的应用,这是一系列功能强大的SQL工具,用于处理窗口或分组数据。 首先,`OVER()`子句允许在单个SQL查询中定义一个...
在Oracle数据库中,可以使用`SELECT sysdate FROM dual`来获取当前系统时间。 4. 触发器的作用: 触发器是一种在特定数据库操作(如INSERT、UPDATE或DELETE)发生时自动执行的存储过程。它们常用于实现复杂的业务...
查看某个特定表的信息,包括创建时间等: ```sql SQL> select object_name, created from user_objects where object_name = upper('&table_name'); ``` **6. 查看特定表的大小** 查询特定表所占用的空间大小...
这篇文档“Oracle语句优化规则汇总.pdf”很可能提供了关于如何提高Oracle数据库查询效率、减少资源消耗的关键信息。以下是一些可能涵盖在文档中的关键知识点: 1. **索引优化**: - 使用合适的索引类型,如B树索引...
Oracle 常用 SQL 语句汇总 Oracle 是一个功能强大且复杂的关系数据库管理系统,它提供了多种 SQL 语句来管理和操作数据库。在本文中,我们将详细介绍 Oracle 中常用的 SQL 语句,包括数据控制语句(DML)、数据定义...
- 这个查询用于获取数据库的创建时间、日志模式等基本信息。 ### 10. 再次查询Oracle版本 **SQL语句:** ```sql select * from v$version; ``` **知识点解释:** - 这个查询与第一个查询相同,用于再次确认Oracle...
同样,理解并调整数据库缓冲区高速缓存和重做日志缓冲区的大小,可以显著影响数据库的响应时间和可恢复性。 综上所述,Oracle数据库的体系结构涉及了复杂的内存管理、后台进程协调以及高效的事务处理机制,这些都是...
- 当使用游标时,应尽量减少打开游标的时间,以提高性能和避免资源浪费。 - 如果游标处理的数据量很大,考虑使用批量处理或集合操作,以减少数据库调用次数。 总结起来,ORACLE中的游标提供了强大的数据处理能力...