Oracle笔记 之 日期时间函数

从时间(日期)获取信息

–获取年份 2011
select extract(year from date ‘2011-05-17’) year from dual;
–获取月份 5
select extract(month from date ‘2011-05-17’) month from dual;
–获取日 17
select extract(day from date ‘2011-05-17’) day from dual;
–获取小时 12
select extract(hour from timestamp ‘2011-05-17 12:14:16’) day from dual;
–获取分 14
select extract(minute from timestamp ‘2011-05-17 12:14:16’) day from dual;
–获取秒 16
select extract(second from timestamp ‘2011-05-17 12:14:16’) day from dual;

TO_CHAR(X [,FORMAT])

将X按FORMAT格式转换成字符串。
X是一个日期，FORMAT是一个规定了X采用何种格式转换的格式字符串。
各种格式介绍如下：

周 (W)

W 的含义是一个月的第几周。是按照ORACLE自定义的标准周来返回周数。
IW是ISO标准周，它的含义是ISO标准周以周别为“主线”，每年最多可以有53个周别，但是每年至少要包含52个周别；如果一年当中第52周别之后至当年的12月31日之间，还有大于或等于4天的话，则定为当年的第53周，否则剩余这些天数被归为下一年的第1周；如果在不足52周别的话，则以下一年的时间来补；每周固定的从周一开始作为本周的第1天，到周日作为本周的第7天；比如：在Oracle中 2012年01月01号依然属于IW周别2011年的第52周的第7天。这个用到的比较多。
WW是ORACLE自定义的标准周，它的含义是每年的1月1日作为当年的第一周的第一天（不管当年的1月1日是星期几）；比如：2014年01年01是周三，在Oracle中被定义为2014年WW的第一周的第一天。一般很少用到。
FMWW该年1月1号（不考虑属星期几）开始至该年该一个星期日为第一周，第二周是从该年第一个星期开始算（这与IW算法相同）一年的最后一周以该年的12月31号做为截止。

–本月第几周 5
select to_char(sysdate, ‘w’) from dual;

–ISO标准周 01
select to_char(sysdate-, ‘iw’) from dual;

–Oracle标准周 53
select to_char(sysdate, ‘ww’) from dual;

–实际周 53
select to_char(sysdate, ‘FMWW’) from dual;

年 (Y)

–末位数字 0
select to_char(sysdate, ‘y’) from dual;

—最后两位数字 20
select to_char(sysdate, ‘yy’) from dual;

—最后三位数字 020
select to_char(sysdate, ‘yyy’) from dual;

—最后四位数字 2020
select to_char(sysdate, ‘yyyy’) from dual;

–英文年份 twenty twenty
select to_char(sysdate, ‘year’) from dual;

季节 (Q)

–第几季度 4
select to_char(sysdate, ‘q’) from dual;

月份 (M)

–月份 12
select to_char(sysdate, ‘mm’) from dual;
–英文简称 dec
select to_char(sysdate, ‘mon’) from dual;
–英文全称 december
select to_char(sysdate, ‘month’) from dual;

日期 (D)

–本周第几日（上周末为本周第一天） 4
select to_char(sysdate, ‘d’) from dual;
–本月第几日 30
select to_char(sysdate, ‘dd’) from dual;
–本年第几日 365
select to_char(sysdate, ‘ddd’) from dual;
–英文简写周日 wed
select to_char(sysdate, ‘dy’) from dual;
–英文全写周日 wednesday
select to_char(sysdate, ‘day’) from dual;
–英文本月第几日 thirty
select to_char(sysdate, ‘ddsp’) from dual;
–英文本月第几日 thirtieth
select to_char(sysdate, ‘ddspth’) from dual;

时钟(H)

–小时 12进制 10
select to_char(sysdate + 0.5, ‘hh’) from dual;
–小时 24进制 10
select to_char(sysdate + 0.5, ‘hh24’) from dual;

分钟 (M)

–分钟 45
select to_char(sysdate, ‘mi’) from dual;

秒钟 (S)

–秒钟 55
select to_char(sysdate, ‘ss’) from dual;

获取当前时间(日期)字符串

–分隔符没有具体要求
–2020-12-30 10:48:01
select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) from dual;
–2020/12/30 10:48:01
select to_char(sysdate, ‘yyyy/mm/dd hh24:mi:ss’) from dual;
–20201230 104801
select to_char(sysdate, ‘yyyymmdd hh24miss’) from dual;

公元元年到现在的时间(小时)

–数字字符 2459214
select to_char(sysdate, ‘j’) from dual;
–英文字符 two million four hundred fifty-nine thousand two hundred fourteen
select to_char(sysdate, ‘jsp’) from dual;

TO_date(X [,FORMAT])

字符串转日期

–yyyy(或者yy)代表年份，mm代表月份，dd代表日,hh代表12进制时钟，hh24代表24进制时钟，mi代表分钟，ss代表秒钟
–注意如果使用hh时钟字段不能大于12
select to_date(‘20201230 20:21:22’, ‘yymmdd hh24:mi:ss’) from dual;
select to_date(‘20201230 20:21:22’, ‘yyyymmdd hh24:mi:ss’) from dual;

获取空日期格式

select to_date(null) from dual;

计算日期差

–30
select floor(sysdate - to_date(‘20201130’, ‘yyyymmdd’)) from dual;

求n天后的时间

–5天后的时间
select sysdate + 5 from dual;

求本月最后一天

–2020-11-30 10:59:30
select last_day(sysdate - 40) from dual;

求下一个周n的时间

–n between 1 and 7 正整数
select next_day(sysdate, 7) from dual;

计算月份差

–2.6599563172043
select months_between(sysdate, to_date(‘20201010’, ‘yyyymmdd’)) from dual;

日期函数

请注意round和trunc的区别

round

–2020-05-14 12:21:34 2020-05-15 2020-01-01 2020-05-01 2020-05-17
–时间，日期，年份，月份，周末
select to_date(‘20200514’, ‘yyyymmdd’),
round(to_date(‘20200514’, ‘yyyymmdd’)),
round(to_date(‘20200514’, ‘yyyymmdd’), ‘year’),
round(to_date(‘20200514’, ‘yyyymmdd’), ‘month’),
round(to_date(‘20200514’, ‘yyyymmdd’), ‘day’)
from dual;

trunc

–2020-05-14 12:21:34 2020-05-14 2020-01-01 2020-05-01 2020-05-10
–时间，日期，年份，月份，周末
select to_date(‘20200514’, ‘yyyymmdd’),
trunc(to_date(‘20200514’, ‘yyyymmdd’)),
trunc(to_date(‘20200514’, ‘yyyymmdd’), ‘year’),
trunc(to_date(‘20200514’, ‘yyyymmdd’), ‘month’),
trunc(to_date(‘20200514’, ‘yyyymmdd’), ‘day’)
from dual;

greatest

select greatest(‘20200514’, ‘2020-05-14’, ‘30-2月-24’) from dual