文章目录
- 1、计算时间函数
- 2、日期和字符转换函数
- 3、日期处理案例汇总
- 3.1、求某天是星期几
- 3.2、找出今年的天数
- 3.3、判断是否是闰年的方法
- 3.3.1、最常规的,先求出年份,然后除以4,能除尽就是闰年,除不尽就是平年
- 3.3.2、判断2月最后一天的日期,28就是平年,29就是闰年
- 3.3.3、判断当年的天数,365是平年,366是闰年
- 3.3.4、判断2月28日的后一天(也就是年初第60天)所在的月份,如果还在2月份,那说明是2月29日,如果在3月份就说明是3月1日了
- 3.4、两个日期间的天数
- 3.5、一年的第几天
- 3.6、查找月的第一天,最后一天
- 3.7、获得小时数
- 3.6、5秒钟一个间隔
- 3.7、计算小时,分,秒,毫秒
- 3.8、计算时间差
- 3.9、改变日期和时间
- 3.10、时间为null的处理方法
- 3.11、不同时区的处理方式
- 3.12、日期格式冲突问题
- 3.13、查找2002-02-28至2002-02-01间除星期一和七的天数
- 3.14、yyyy与rrrr的区别
参考资料:Database SQL Language Reference
https://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm
1、计算时间函数
1.1、numtodsinterval函数
numtodsinterval(,) ,x是一个数字,c是一个字符串,
表明x的单位,这个函数把x转为interval day to second数据类型
- 常用的单位有 (‘day’,‘hour’,‘minute’,‘second’)
举例:
SQL> select sysdate,sysdate+numtodsinterval(3,'hour') as res from dual;
SYSDATE RES
------------------- -------------------
2007-09-05 01:45:34 2007-09-05 04:45:34
1.2、numtoyminterval函数
numtoyminterval 与numtodsinterval函数类似,将x转为interval year to month数据类型
- 常用的单位有’year’,‘month’
举例:
SQL> select sysdate,sysdate+numtoyminterval(3,'year') as res from dual;
SYSDATE RES
------------------- -------------------
2007-09-05 01:54:53 2010-09-05 01:54:53
备注:+号改为-号,也可以取一个月前或者一个小时前的日期。
1.3、months_between函数
此函数用来查找两个日期之间间隔的月份,举例如下:
select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1
select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1.03225806451613
1.4、add_months函数
add_months 函数主要是对日期函数进行操作,举例子进行说明
add_months 有两个参数,第一个参数是日期,第二个参数是对日期进行加减的数字(以月为单位的)
--表示三个月之后的日期
SYS@orcl11g>select add_months(sysdate,3) from dual;
ADD_MONTH
---------
08-JAN-20
--表示三个月之前的日期
SYS@orcl11g>select add_months(sysdate,-3) from dual;
ADD_MONTH
---------
08-JUL-19
1.5、next_day函数
返回值为日期,Next_day(日期, 参数)
参数解释:一周的第一天是周日,取下一个日期
参数 | 解释 |
---|---|
Monday - Sunday | 取下一个星期几, DAY |
Mon - Sun | 取下一个星期几,上面的简写, DY |
1 - 7 | 下一周的第几天,1表示下周第一天,2表示下周第二天, D |
返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
以2019年10月8号这天为例2019年10月6号为本周第一天(周日),2019年10月13号为下周第一天(周日):
SYS@orcl11g>select sysdate,trunc(sysdate,'DAY'),trunc(sysdate,'D'),trunc(sysdate,'DY'),
next_day(sysdate,'Monday'),next_day(sysdate,'Mon'),next_day(sysdate,1) from dual;
SYSDATE TRUNC(SYS TRUNC(SYS TRUNC(SYS NEXT_DAY( NEXT_DAY( NEXT_DAY(
--------- --------- --------- --------- --------- --------- ---------
08-OCT-19 06-OCT-19 06-OCT-19 06-OCT-19 14-OCT-19 14-OCT-19 13-OCT-19
next_day(sysdate,7) 貌似有bug,出现了错误的结果,最好还是用前两种表示方式。
1.6、last_day函数
last_day(某个日期) 返回特定日期所在月份的最后一天,此函数比较简单。
select last_day(sysdate) from dual;—返回当前日期所在月份的最后一天的当前时间
1.7、
2、日期和字符转换函数
2.1、to_char和to_date
2.1.1、格式说明
TO_DATE格式(以时间:2007-11-02 13:45:25为例)
Year:
参数 | 解释 | 显示值 |
---|---|---|
yy | two digits 两位年 | 显示值:07 |
yyy | three digits 三位年 | 显示值:007 |
yyyy | four digits 四位年 | 显示值:2007 |
Month:
参数 | 解释 | 显示值 |
---|---|---|
mm | number 两位月 | 显示值:11 |
mon | abbreviated 字符集表示 | 显示值:11月,若是英文版,显示nov month |
Day:
参数 | 解释 | 显示值 |
---|---|---|
dd | number 当月第几天 | 显示值:02 |
ddd | number 当年第几天 | 显示值:02 |
dy | abbreviated 当周第几天简写 | 显示值:星期五,若是英文版,显示fri |
day | spelled out 当周第几天全写 | 显示值:星期五,若是英文版,显示friday ddspth spelled out, ordinal twelfth |
Hour:
参数 | 解释 | 显示值 |
---|---|---|
hh | two digits 12小时进制 | 显示值:01 |
hh24 | two digits 24小时进制 | 显示值:13 |
Minute:
参数 | 解释 | 显示值 |
---|---|---|
mi | two digits 60进制 | 显示值:45 |
Second:
参数 | 解释 | 显示值 |
---|---|---|
ss | two digits 60进制 | 显示值:25 |
其它:
参数 | 解释 | 显示值 |
---|---|---|
Q | digit 季度 | 显示值:4 |
WW | digit 当年第几周 | 显示值:44 |
W | digit 当月第几周 | 显示值:1 |
24小时格式下时间范围为: 0:00:00 - 23:59:59…
12小时格式下时间范围为: 1:00:00 - 12:59:59 …
2.1.2、相互转换
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串
select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年
select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月
select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日
select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时
select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分
select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual;//字符串转换为日期
select to_char(to_date(222,'J'),'Jsp') from dual;//显示Two Hundred Twenty-Two
2.2、trunc()
trunc[截断到最接近的日期,单位为天] ,返回的是日期类型。
看下面例子:
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sysdate S1, --返回当前时间
trunc(sysdate) S2, --返回当前日期,无时分秒
trunc(sysdate,'year') YEAR, --返回当前年的1月1日,无时分秒
trunc(sysdate,'month') MONTH , --返回当前月的1日,无时分秒
trunc(sysdate,'day') DAY --返回当前星期的星期天,无时分秒
from dual;
- 表示上周的例子
select trunc(sysdate,'iw') - 7 from dual;---上周一
select trunc(sysdate,'iw') - 1 from dual;--上周日
2.3、round
round舍入到最接近的日期,注意区别于trunc()
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY
from dual;
2.4、extract()、greatest
2.4.1、extract()函数
oracle中extract()函数用于从一个date或者interval类型中截取到特定的部分。
- 从一个date类型中截取年月日
select extract (year from sysdate) year, extract (month from sysdate) month, extract (day from sysdate) day from dual;
select extract (year from date '2019-05-25') year, extract (month from date'2019-05-25') month, extract (day from date '2011-05-04') day from dual;
- 从timestamp中获取年月日时分秒
select
extract(year from systimestamp) year
,extract(month from systimestamp) month
,extract(day from systimestamp) day
,extract(minute from systimestamp) minute
,extract(second from systimestamp) second
,extract(timezone_hour from systimestamp) th
,extract(timezone_minute from systimestamp) tm
,extract(timezone_region from systimestamp) tr
,extract(timezone_abbr from systimestamp) ta
from dual;
- 获取两个日期之间的具体时间间隔,天、小时、分、秒分别计算间隔
select
extract (day from dt2 - dt1) day,
extract (hour from dt2 - dt1) hour,
extract (minute from dt2 - dt1) minute,
extract (second from dt2 - dt1) second
from
(
select
to_timestamp ('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1,
to_timestamp ('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2
from
dual;
)
2.4.2、greatest()函数
GREATEST(expr_1, expr_2,…expr_n)函数从表达式(列、常量、计算值)expr_1, expr_2, … expr_n等中找出最大的数返回。在比较时,OracIe会自动按表达式的数据类型进行比较,以expr_1的数据类型为准。
下面以比较日期类型数据为例:需要显示to_date,不会帮你隐士转换
SELECT GREATEST(sysdate,TO_DATE('2019-08-01','YYYY-MM-DD')) A FROM DUAL;
3、日期处理案例汇总
3.1、求某天是星期几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
星期一
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
monday
设置日期语言:
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
3.2、找出今年的天数
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual;
3.3、判断是否是闰年的方法
闰年有366天,平年365天
3.3.1、最常规的,先求出年份,然后除以4,能除尽就是闰年,除不尽就是平年
SELECT (CASE
MOD(to_number(to_char(SYSDATE,'yyyy')),4)--MOD为求余函数
WHEN 0 THEN '闰年' ELSE '平年' END) 年
FROM dual;
3.3.2、判断2月最后一天的日期,28就是平年,29就是闰年
SELECT (CASE to_char(--用TO_CHAR取出日期
last_day(--LAST_DAY函数为求所在月份最后一天的日期
add_months(--ADD_MONTHS函数为加月份数,1月1日加1个月就是2月1日
TRUNC(SYSDATE,'yy'),1)),'dd')--用TRUNC函数求年初第一天,也就是1月1日
WHEN '28' THEN '平年' ELSE '闰年' END) 年
FROM dual;
3.3.3、判断当年的天数,365是平年,366是闰年
SELECT (CASE TRUNC(add_months(SYSDATE,12),'yy')--当前时间加12个月,能确保是明年这个时间,再求明年1月1日
-TRUNC(SYSDATE,'yy') --再减去今年1月1日,就是今年的天数
WHEN 365 THEN '平年' ELSE '闰年' END) 年
FROM dual;
3.3.4、判断2月28日的后一天(也就是年初第60天)所在的月份,如果还在2月份,那说明是2月29日,如果在3月份就说明是3月1日了
SELECT (CASE to_char(TRUNC(SYSDATE,'yy')+59,'mm')--第60天,所以加59
WHEN '03' THEN '平年' ELSE '闰年' END) 年 FROM dual;
3.4、两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
3.5、一年的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
3.6、查找月的第一天,最后一天
SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
FROM dual
3.7、获得小时数
方法一:extract()找出日期或间隔值的字段值
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from dual;
方法二:
SQL> select sysdate ,to_char(sysdate,'hh') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07
SQL> select sysdate ,to_char(sysdate,'hh24') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19
3.6、5秒钟一个间隔
Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS'),
TO_CHAR(sysdate,'SSSSS') from dual;
2002-11-1 9:55:00 35786
SSSSS表示5位秒数
3.7、计算小时,分,秒,毫秒
select
Days,
A,
TRUNC(A*24) Hours,
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
from
(
select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from dual
)
select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');
说明:
floor((date2-date1) /365) 作为年
floor((date2-date1, 365) /30) 作为月
d(mod(date2-date1, 365), 30)作为日.
3.8、计算时间差
注:oracle时间差是以天数为单位,所以换算成年月,日
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual //时间差-年
select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual //时间差-月
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual //时间差-天
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual //时间差-时
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual //时间差-分
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒
3.9、改变日期和时间
注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-年
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual //改变时间-月
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-时
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-分
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-秒
3.10、时间为null的处理方法
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
注意要用TO_DATE(null)
3.11、不同时区的处理方式
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
from dual;
3.12、日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: ‘01-Jan-01’
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中写:
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
注意我这只是举了NLS_DATE_LANGUAGE,当然还有很多,
查看当前日期格式参数方式:
select * from nls_session_parameters where PARAMETER ='NLS_DATE_LANGUAGE';
select * from V$NLS_PARAMETERS where PARAMETER ='NLS_DATE_LANGUAGE';
3.13、查找2002-02-28至2002-02-01间除星期一和七的天数
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )
not in ( '1', '7' )
3.14、yyyy与rrrr的区别
先看例子:
SYS@orcl11g>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SYS@orcl11g>select to_date('09/01/02','YYYY-MM-DD') YYYY ,to_date ('09/01/02','RRRR-MM-DD') RRRR
2 from dual;
YYYY RRRR
------------------- -------------------
0009-01-02 00:00:00 2009-01-02 00:00:00
发现yyyy不是我们想要的2009年,为什么呢?
RRRR : 这边说得如果你输入的是2位数字则会转换为,四位年,如果是四位年,则保持不变。
YYYY:只是将同一位置上的字符转换为要求的字符,如果为数不够则用0 补充。