一, oracle sql 日期比较
在今天之前: select * from up_date where update <to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update <= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
在今天只后: select * from up_date where update> to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update>= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
精确时间: select * from up_date where update = to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
在某段时间内: select * from up_date where update between to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
- select * from up_date where update <to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and update> to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
- select * from up_date where update <= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and update>= to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24: 二, Oracle 获取当前日期及日期格式 获取系统日期: SYSDATE()
格式化日期: TO_CHAR(SYSDATE(),'YY/MM/DD HH24:MI:SS)
或 TO_DATE(SYSDATE(),'YY/MM/DD HH24:MI:SS)
格式化数字: TO_NUMBER
注: TO_CHAR 把日期或数字转换为字符串
- TO_CHAR(number, '格式')
- TO_CHAR(salary, '$99,999.99')
- TO_CHAR(date, '格式')
TO_DATE 把字符串转换为数据库中的日期类型
TO_DATE(char, '格式')
TO_NUMBER 将字符串转换为数字
TO_NUMBER(char, '格式')
返回系统日期, 输出 25-12 月 - 09
select sysdate from dual;
mi 是分钟, 输出 2009-12-25 14:23:31
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
mm 会显示月份, 输出 2009-12-25 14:12:31
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;
输出 09-12-25 14:23:31
select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual
输出 2009-12-25 14:23:31
select to_date('2009-12-25 14:23:31','yyyy-mm-dd,hh24:mi:ss') from dual
而如果把上式写作:
select to_date('2009-12-25 14:23:31','yyyy-mm-dd,hh:mi:ss') from dual
则会报错, 因为小时 hh 是 12 进制, 14 为非法输入, 不能匹配.
输出 $10,000,00 :
select to_char(1000000,'$99,999,99') from dual;
输出 RMB10,000,00 :
select to_char(1000000,'L99,999,99') from dual;
输出 1000000.12 :
- select trunc(to_number('1000000.123'),2) from dual;
- select to_number('1000000.123') from dual;
转换的格式:
表示 year 的: y 表示年的最后一位 ,
yy 表示年的最后 2 位 ,
yyy 表示年的最后 3 位 ,
yyyy 用 4 位数表示年
表示 month 的: mm 用 2 位数字表示月 ,
mon 用简写形式, 比如 11 月或者 nov ,
month 用全称, 比如 11 月或者 november
表示 day 的: dd 表示当月第几天 ,
ddd 表示当年第几天 ,
dy 当周第几天, 简写, 比如星期五或者 fri ,
day 当周第几天, 全称, 比如星期五或者 friday
表示 hour 的: hh 2 位数表示小时 12 进制,
hh24 2 位数表示小时 24 小时
表示 minute 的: mi 2 位数表示分钟
表示 second 的: ss 2 位数表示秒 60 进制
表示季度的: q 一位数 表示季度 (1-4)
另外还有 ww 用来表示当年第几周 w 用来表示当月第几周.
24 小时制下的时间范围: 00:00:00-23:59:59
12 小时制下的时间范围: 1:00:00-12:59:59
数字格式: 9 代表一个数字
0 强制显示 0
$ 放置一个 $ 符
L 放置一个浮动本地货币符
. 显示小数点
, 显示千位指示符
补充:
当前时间减去 7 分钟的时间
select sysdate,sysdate - interval '7' MINUTE from dual;
当前时间减去 7 小时的时间
select sysdate - interval '7' hour from dual;
当前时间减去 7 天的时间
select sysdate - interval '7' day from dual;
当前时间减去 7 月的时间
select sysdate,sysdate - interval '7' month from dual;
当前时间减去 7 年的时间
select sysdate,sysdate - interval '7' year from dual;
时间间隔乘以一个数字
select sysdate,sysdate - 8*interval '7' hour from dual;
含义解释:
Dual 伪列
Dual 是 Oracle 中的一个实际存在的表, 任何用户均可读取, 常用在没有目标表的 select 语句块中.
不同系统可能返回日期的格式不一样.
返回当前连接的用户: select user from dual; 三, 前后第几天获取昨天:
select trunc(SYSDATE-1) from dual;
检查一下:
select to_char (trunc(SYSDATE-1),'yyyy-mm-dd HH24:MI:SS') from dual;
获取上个月第一天 00:00:00:
select add_months(trunc(sysdate,'MON'),-1) from dual
select add_months(trunc(sysdate,'MON'),-13) from dual 也可以
获取上个月今天 00:00:00:
SELECT trunc(add_months(sysdate,-1))FROM dual
获得本季度第一天
SELECT TRUNC(add_months(SYSDATE,0),'Q') FROM dual
获得上季度第一天
SELECT TRUNC(add_months(SYSDATE,-3),'Q') FROM dual
获得去年 1 月 1 日
to_char(add_months(trunc(sysdate, 'Year'), -12), 'YYYY-MM-DD')
between and 前面的时间小后面的时间大
to_date('20110105','YYYYMMDD')-7 此处 -7 代表天
四, Oracle 时间函数常用的时间格式 在 oracle 中有 yyyy-mm-dd hh24:mi:ss 而在 Java 中有些区别 为 yyyy-MM-dd HH:mm:ss;
to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')
常用函数及示例: 1.Sysdate 当前日期和时间
- SQL> Select sysdate from dual;
- SYSDATE
- ----------
21-6 月 -05
2.Last_day 本月最后一天
- SQL> Select last_day(sysdate) from dual;
- LAST_DAY(S
- ----------
30-6 月 -05
3.Add_months(d,n) 当前日期 d 后推 n 个月
用于从一个日期值增加或减少一些月份
- date_value:=add_months(date_value,number_of_months)
- SQL> Select add_months(sysdate,2) from dual;
- ADD_MONTHS
- ----------
21-8 月 -05
4.Months_between(f,s) 日期 f 和 s 间相差月数
- SQL> select months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))from dual;
- MONTHS_BETWEEN(SYSDATE,TO_DATE('2005-11-12','YYYY-MM-DD'))
- ----------------------------------------------------------
- -4.6966741
- 5.NEXT_DAY(d, day_of_week)
返回由 "day_of_week" 命名的, 在变量 "d" 指定的日期之后的第一个工作日的日期. 参数 "day_of_week" 必须为该星期中的某一天.
- SQL> SELECT next_day(to_date('20050620','YYYYMMDD'),1) FROM dual;
- NEXT_DAY(T
- ----------
26-6 月 -05
6.current_date()返回当前会话时区中的当前日期
- date_value:=current_date
- SQL> column sessiontimezone for a15
- SQL> select sessiontimezone,current_date from dual;
- SESSIONTIMEZONE CURRENT_DA
- --------------- ----------
+08:00 13-11 月 - 03
SQL> alter session set time_zone='-11:00' 2 /
会话已更改.
- SQL> select sessiontimezone,current_timestamp from dual;
- SESSIONTIMEZONE CURRENT_TIMESTAMP
- --------------- ------------------------------------
-11:00 12-11 月 - 03 04.59.13.668000 下午 -11:00
7.current_timestamp()以 timestamp with time zone 数据类型返回当前会话时区中的当前日期
- SQL> select current_timestamp from dual;
- CURRENT_TIMESTAMP
- ---------------------------------------------------------------------------
21-6 月 -05 10.13.08.220589 上午 +08:00
8.dbtimezone()返回时区
- SQL> select dbtimezone from dual;
- DBTIME
- ------
- -08:00
9.extract()找出日期或间隔值的字段值
- date_value:=extract(date_field from [datetime_value|interval_value])
- SQL> select extract(month from sysdate) "This Month" from dual;
- This Month
- ----------
- 6
- SQL> select extract(year from add_months(sysdate,36)) "Years" from dual;
- Years
- ----------
- 2008
10.localtimestamp()返回会话中的日期和时间
- SQL> select localtimestamp from dual;
- LOCALTIMESTAMP
- ---------------------------------------------------------------------------
21-6 月 -05 10.18.15.855652 上午
常用日期数据格式(该段为摘抄)
Y 或 YY 或 YYY 年的最后一位, 两位或三位 Select to_char(sysdate,'YYY') from dual; 002 表示 2002 年
SYEAR 或 YEAR SYEAR 使公元前的年份前加一负号 Select to_char(sysdate,'SYEAR') from dual; -1112 表示公元前 111 2 年
Q 季度, 1~3 月为第一季度 Select to_char(sysdate,'Q') from dual; 2 表示第二季度
MM 月份数 Select to_char(sysdate,'MM') from dual; 12 表示 12 月
RM 月份的罗马表示 Select to_char(sysdate,'RM') from dual; IV 表示 4 月
Month 用 9 个字符长度表示的月份名 Select to_char(sysdate,'Month') from dual; May 后跟 6 个空格表示 5 月
WW 当年第几周 Select to_char(sysdate,'WW') from dual; 24 表示 2002 年 6 月 13 日为第 24 周
W 本月第几周 Select to_char(sysdate,'W') from dual; 2002 年 10 月 1 日为第 1 周
DDD 当年第几, 1 月 1 日为 001,2 月 1 日为 032 Select to_char(sysdate,'DDD') from dual; 363 2002 年 1 2 月 2 9 日为第 363 天
DD 当月第几天 Select to_char(sysdate,'DD') from dual; 04 10 月 4 日为第 4 天
D 周内第几天 Select to_char(sysdate,'D') from dual; 5 2002 年 3 月 14 日为星期一
DY 周内第几天缩写 Select to_char(sysdate,'DY') from dual; SUN 2002 年 3 月 24 日为星期天
HH 或 HH12 12 进制小时数 Select to_char(sysdate,'HH') from dual; 02 午夜 2 点过 8 分为 02
HH24 24 小时制 Select to_char(sysdate,'HH24') from dual; 14 下午 2 点 08 分为 14
MI 分钟数(0~59) Select to_char(sysdate,'MI') from dual; 17 下午 4 点 17 分
SS 秒数(0~59) Select to_char(sysdate,'SS') from dual; 22 11 点 3 分 22 秒
提示注意不要将 MM 格式用于分钟(分钟应该使用 MI).MM 是用于月份的格式, 将它用于分钟也能工作, 但结果是错误的.
现在给出一些实践后的用法:
1. 上月末天:
- SQL> select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from
- dual;
- LASTDAY
- ----------
- 2005-05-31
2. 上月今天
- SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;
- PRETODAY
- ----------
- 2005-05-21
3. 上月首天
- SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;
- FIRSTDAY
- ----------
- 2005-05-01
4. 按照每周进行统计
- SQL> select to_char(sysdate,'ww') from dual group by to_char(sysdate,'ww');
- TO
- --
- 25
5. 按照每月进行统计
- SQL> select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm');
- TO
- --
- 06
6. 按照每季度进行统计
- SQL> select to_char(sysdate,'q') from dual group by to_char(sysdate,'q');
- T
- -
- 2
7. 按照每年进行统计
- SQL> select to_char(sysdate,'yyyy') from dual group by to_char(sysdate,'yyyy');
- TO_C
- ----
- 2005
8. 要找到某月中所有周五的具体日期
- select to_char(t.d,'YY-MM-DD') from (
- select trunc(sysdate, 'MM')+rownum-1 as d
- from dba_objects
- where rownum <32) t
where to_char(t.d, 'MM') = to_char(sysdate, 'MM') -- 找出当前月份的周五的日期
- and trim(to_char(t.d, 'Day')) = '星期五'
- --------
- 03-05-02
- 03-05-09
- 03-05-16
- 03-05-23
- 03-05-30
如果把 where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成 sysdate-90, 即为查找当前月份的前三个月中的每周五的日期.
9.oracle 中时间运算
内容如下:
1,oracle 支持对日期进行运算
2, 日期运算时是以天为单位进行的
3, 当需要以分秒等更小的单位算值时, 按时间进制进行转换即可
4, 进行时间进制转换时注意加括号, 否则会出问题
SQL> alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';
会话已更改.
- SQL> set serverout on
- SQL> declare
- 2 DateValue date;
- 3 begin
- 4 select sysdate into DateValue from dual;
- 5 dbms_output.put_line('源时间:'||to_char(DateValue));
- 6 dbms_output.put_line('源时间减 1 天:'||to_char(DateValue-1));
- 7 dbms_output.put_line('源时间减 1 天 1 小时:'||to_char(DateValue-1-1/24));
- 8 dbms_output.put_line('源时间减 1 天 1 小时 1 分:'||to_char(DateValue-1-1/24-1/(24*60)));
- 9 dbms_output.put_line('源时间减 1 天 1 小时 1 分 1 秒:'||to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60)));
- 10 end;
- 11 /
源时间: 2003-12-29 11:53:41
源时间减 1 天: 2003-12-28 11:53:41
源时间减 1 天 1 小时: 2003-12-28 10:53:41
源时间减 1 天 1 小时 1 分: 2003-12-28 10:52:41
源时间减 1 天 1 小时 1 分 1 秒: 2003-12-28 10:52:40
PL/SQL 过程已成功完成.
在 Oracle 中实现时间相加处理
-- 名称: Add_Times
-- 功能: 返回 d1 与 NewTime 相加以后的结果, 实现时间的相加
-- 说明: 对于 NewTime 中的日期不予考虑
-- 日期: 2004-12-07
-- 版本: 1.0
-- 作者: Kevin
- create or replace function Add_Times(d1 in date,NewTime in date) return date
- is
- hh number;
- mm number;
- ss number;
- hours number;
- dResult date;
- begin
-- 下面依次取出时, 分, 秒
- select to_number(to_char(NewTime,'HH24')) into hh from dual;
- select to_number(to_char(NewTime,'MI')) into mm from dual;
- select to_number(to_char(NewTime,'SS')) into ss from dual;
-- 换算出 NewTime 中小时总和, 在一天的百分几
hours := (hh + (mm / 60) + (ss / 3600))/ 24;
-- 得出时间相加后的结果
- select d1 + hours into dResult from dual;
- return(dResult);
- end Add_Times;
-- 测试用例
-- select Add_Times(sysdate,to_date('2004-12-06 03:23:00','YYYY-MM-DD HH24:MI:SS')) from dual
在 Oracle9i 中计算时间差
计算时间差是 Oracle DATA 数据类型的一个常见问题. Oracle 支持日期计算, 你可以创建诸如 "日期 1 - 日期 2" 这样的表达式来计算这两个日期之间的时间差.
一旦你发现了时间差异, 你可以使用简单的技巧来以天, 小时, 分钟或者秒为单位来计算时间差. 为了得到数据差, 你必须选择合适的时间度量单位, 这样就可以进行数据格式隐藏.
使用完善复杂的转换函数来转换日期是一个诱惑, 但是你会发现这不是最好的解决方法.
round(to_number(end-date-start_date))- 消逝的时间(以天为单位)
round(to_number(end-date-start_date)*24)- 消逝的时间(以小时为单位)
round(to_number(end-date-start_date)*1440)- 消逝的时间(以分钟为单位)
显示时间差的默认模式是什么? 为了找到这个问题的答案, 让我们进行一个简单的 SQL *Plus 查询.
- SQL> select sysdate-(sysdate-3) from dual;
- SYSDATE-(SYSDATE-3)
- -------------------
- 3
这里, 我们看到了 Oracle 使用天来作为消逝时间的单位, 所以我们可以很容易的使用转换函数来把它转换成小时或者分钟. 然而, 当分钟数不是一个整数时, 我们就会遇到放置小数点的问题.
- Select
- (sysdate-(sysdate-3.111))*1440
- from
- dual;
- (SYSDATE-(SYSDATE-3.111))*1440
- ------------------------------
- 4479.83333
当然, 我们可以用 ROUND 函数 (即取整函数) 来解决这个问题, 但是要记住我们必须首先把 DATE 数据类型转换成 NUMBER 数据类型.
- Select
- round(to_number(sysdate-(sysdate-3.111))*1440)
- from
- dual;
- ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)
- ----------------------------------------------
- 4480
我们可以用这些函数把一个消逝时间近似转换成分钟并把这个值写入 Oracle 表格中. 在这个例子里, 我们有一个离线 (logoff) 系统级触发机制来计算已经开始的会话时间并把它放入一个 Oracle STATSPACK USER_LOG 扩展表格之中.
- Update
- perfstat.stats$user_log
- set
- elapsed_minutes =
- round(to_number(logoff_time-logon_time)*1440)
- where
- user = user_id
- and
- elapsed_minutes is NULL;
查出任一年月所含的工作日
- CREATE OR REPLACE FUNCTION Get_WorkingDays(
- ny IN VARCHAR2
- ) RETURN INTEGER IS
- /*------------------------------------------------------------------------------------------
- 函数名称: Get_WorkingDays
- 中文名称: 求某一年月中共有多少工作日
- 作者姓名: XINGPING
- 编写时间: 2004-05-22
- 输入参数: NY: 所求包含工作日数的年月, 格式为 yyyymm, 如 200405
- 返 回 值: 整型值, 包含的工作日数目.
- 算法描述:
- 1). 列举出参数给出的年月中的每一天. 这里使用了一个表 (ljrq 是我的库中的一张表. 这个表可以是有权访问的, 记录条数至少为 31 的任意一张表或视图) 来构造出某年月的每一天.
- 2). 用这些日期和一个已知星期几的日期相减(2001-12-30 是星期天), 所得的差再对 7 求模. 如果所求年月在 2001-12-30 以前, 那么所得的差既是负数, 求模后所得值范围为大于 - 6, 小于 0, 如 - 1 表示星期六, 故先将求模的结果加 7, 再求 7 的模.
- 3). 过滤掉结果集中值为 0 和 6 的元素, 然后求 count, 所得即为工作日数目.
- -------------------------------------------------------------------------------------------------*/
- Result INTEGER;
- BEGIN
- SELECT COUNT(*) INTO Result
- FROM (SELECT MOD(MOD(q.rq-to_date('2001-12-30','yyyy-mm-dd'),7),7) weekday
- FROM ( SELECT to_date(ny||t.dd,'yyyymmdd') rq
- FROM (SELECT substr(100+ROWNUM,2,2) dd
- FROM ljrq z WHERE Rownum<=31
- ) t
- WHERE to_date(ny||t.dd,'yyyymmdd')
- BETWEEN to_date(ny,'yyyymm')
- AND last_day(to_date(ny,'yyyymm'))
- )q
- ) a
- WHERE a.weekday NOT IN(0,6);
- RETURN Result;
- END Get_WorkingDays;
- ______________________________________
还有一个版本
- CREATE OR REPLACE FUNCTION Get_WorkingDays(
- ny IN VARCHAR2
- ) RETURN INTEGER IS
- /*-----------------------------------------------------------------------------------------
- 函数名称: Get_WorkingDays
- 中文名称: 求某一年月中共有多少工作日
- 作者姓名: XINGPING
- 编写时间: 2004-05-23
- 输入参数: NY: 所求包含工作日数的年月, 格式为 yyyymm, 如 200405
- 返 回 值: 整型值, 包含的工作日数目.
- 算法描述: 使用 Last_day 函数计算出参数所给年月共包含多少天, 根据这个值来构造一个循环. 在这个循环中先求这个月的每一天与一个已知是星期天的日期 (2001-12-30 是星期天) 的差, 所得的差再对 7 求模. 如果所求日期在 2001-12-30 以前, 那么所得的差既是负数, 求模后所得值范围为大于 - 6, 小于 0, 如 - 1 表示星期六, 故先将求模的结果加 7, 再求 7 的模. 如过所得值不等于 0 和 6(即不是星期六和星期天), 则算一个工作日.
- ----------------------------------------------------------------------------------------*/
- Result INTEGER := 0;
myts INTEGER; -- 所给年月的天数
scts INTEGER; -- 某天距 2001-12-30 所差的天数
- rq DATE;
- djt INTEGER := 1; --
- BEGIN
- myts := to_char(last_day(to_date(ny,'yyyymm')),'dd');
- LOOP
- rq := TO_date(ny||substr(100+djt,2),'yyyymmdd');
- scts := rq - to_date('2001-12-30','yyyy-mm-dd');
- IF MOD(MOD(scts,7)+7,7) NOT IN(0,6) THEN
- Result := Result + 1;
- END IF;
- djt := djt + 1;
- EXIT WHEN djt>myts;
- END LOOP;
- RETURN Result;
- END Get_WorkingDays;
以上两个版本的比较
第一个版本一条 SQL 语句就可以得出结果, 不需要编程就可以达到目的. 但需要使用任意一张有权访问的, 记录条数至少为 31 的一张表或视图.
第二个版本需要编程, 但不需要表或者视图.
这两个版本都还存在需要完善的地方, 即没有考虑节日, 如五一, 十一, 元旦, 春节这些节假期都没有去除. 这些节假日应该维护成一张表, 然后通过查表来去除这些节假日.
来源: http://www.bubuko.com/infodetail-2721101.html