Oracle函数 – 日期函数详解

共计 9873 个字符，预计需要花费 25 分钟才能阅读完成。

Oracle 中的时间类型只有 date 和 TIMESTAMP，TIMESTAMP 是比 date 更精确的类型。日期时间函数用于处理时间类型的数据，Oracle 以 7 位数字格式来存放日期数据，包括世纪、年、月、日、小时、分钟、秒，并且默认日期显式格式为“DD-MON-YY”。在 Oracle 中准确来说一个礼拜是从星期日开始到星期六结束的，其中时间差以天数为单位。

SYSDATE：取得当前的日期和时间, 类型是 DATE. 它没有参数. 但在分布式 SQL 语句中使用时,SYSDATE 返回本地数据库的日期和时间.

SYSTIMESTAMP：9i 新增函数，返回当前系统的日期时间及时区。

多种日期格式：

YYYY：四位表示的年份 
YYY，YY，Y：年份的最后三位、两位或一位，缺省为当前世纪 
IYYY:ISO 标准的四位年份
MM：01~12 的月份编号 
MON：缩写字符集表示 
MONTH：全拼字符集表示的月份，右边用空格填补
Q：季度
W：当月第几周
WW：当年第几周 
IW:ISO 标准的年中的第几周
D：当周第几天 
DD：当月第几天 
DDD：当年第几天 
DY：缩写字符集表示
DAY：全拼字符集表示的天 如（星期六）
HH，HH12：一天中的第几个小时，12 进制表示法 
HH24：一天中的第几个小时，取值为 00~23 
MI：一小时中的分钟 
SS：一分钟中的秒 

SSSS：从午夜开始过去的秒数 

select to_char(sysdate, ‘yyyy’) 年,
      to_char(sysdate, ‘mm’) 月,
      to_char(sysdate, ‘DD’) 日,
      to_char(sysdate, ‘HH24’) 时,
      to_char(sysdate, ‘MI’) 分,
      to_char(sysdate, ‘SS’) 秒,
      to_char(sysdate, ‘DAY’) 天,
      to_char(sysdate, ‘Q’) 第几季度,
      to_char(sysdate, ‘W’) 当月第几周,
      to_char(sysdate, ‘WW’) 当年第几周,
      to_char(sysdate, ‘D’) 当周第几天,
      to_char(sysdate, ‘DDD’) 当年第几天   
  from dual;

结果：

1、与 date 操作关系最大的就是两个转换函数：to_date(char[fmt[,’nls_param’]]),to_char(date[,fmt,[,nls_param]]) 

1.1、to_date(char[fmt[,’nls_param’]])：将字符类型按一定格式转化为日期类型
具体用法:to_date(‘2004-11-27′,’yyyy-mm-dd’), 前者为字符串，后者为转换日期格式，注意，前后两者要一一对应。
当时间为 null 时的用法：select to_date(null) from dual;
结果：

1.2、to_char(date[,fmt,[,nls_param]]): 将日期转按一定格式换成字符类型，fmt,nls_param 为可选项，fmt 指定了要转化的格式，nls_param 指定了返回日期所使用的语言

结果：

select to_char(to_date(‘2018-05-11′,’yyyy-mm-dd’),’DAY’) from dual;
结果：
select to_char(to_date(‘2018-05-11′,’yyyy-mm-dd’),’DAY’,’NLS_DATE_LANGUAGE=American’) from dual;

结果：

1.4、设置日期语言：也就是设置 nls_session_parameters 视图的值
查看参数值：select * from nls_session_parameters where parameter = ‘NLS_DATE_LANGUAGE’;
   修改时间以什么区域方式显示，比如是要显示 MONDAY 还是要显示星期一：alter session set NLS_DATE_LANGUAGE=’American’;

       日期格式：alter session set NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;

2、显示数字的英文读法：这个是 oracle 的特殊用法，也不常用。

select to_char(to_date(222,’J’),’Jsp’) from dual;

结果：Two Hundred Twenty-Two

3、TO_TIMESTAMP(char[fmt[,’nls_param’]])函数：应注意 char、fmt、nls_param 之间的对应关系。

结果：

select cast(sysdate as timestamp) date_to_timestamp from dual;

4、TO_TIMESTAMP_TZ(char[fmt[,’nls_param’]])函数：将符合特定日期和时间格式的字符串转变为 TIMESTAMP WITH TIME ZONE 类型。

结果：

5、current_date、current_timestamp、localtimestamp、sessiontimezone、dbtimezone 函数

current_date:9i 新增函数，返回当前会话时区所对应的日期时间(date 型)
current_timestamp：以 timestamp with time zone 数据类型返回当前会话时区所对应的日期时间。
localtimestamp：返回当前会话时区的日期时间
sessiontimezone：返回会话时区(字符型)

dbtimezone：返回数据库所在时区

SELECT current_date,current_timestamp,localtimestamp,sessiontimezone,dbtimezone from dual;

结果：

ZOON 时区：

GMT：格林威治时间（老的时间计量标准）
UTC：协调世界时间（我们现在用的时间标准）, 比 GTM 更加标准，UTC=GMT
CST：中央标准时间 CDT：中部夏令时
PST：太平洋时间   PDT：太平洋夏令时
EST: 东部标准时间  EDT：东部夏令时间     EST=UTC-5;

6、FROM_TZ(timaezone_stamp,timezone_value)函数：将时区值和 TIMESTAMP(时间戳)转换为 TIMESTAMP WITH TIME ZONE 值。

结果：

7、TZ_OFFSET(time_zone_name||sessiontimezone||dbtimezone)：返回特定时区与 UTC 相比的时区偏移。

结果：

8、SYS_EXTRACT_UTC(timestamp)函数：将一个 timestamptz 转换成 UTC 时区的本地时间

结果：

9、NEW_TIME(date,timezone1,timezone2): 计算当时区 timezone1 中的日期和时间是 date 时候, 返回时区 timezone2 中的日期和时间

select NEW_TIME(to_date('2018-05-14 12:00:00','yyyy-mm-dd hh24:mi:ss'),'GMT','EST'),to_date('2018-05-14 12:00:00','yyyy-mm-dd hh24:mi:ss') from dual;

10、ADD_MONTHS(d,n)函数：在某一个日期 d 上，加上指定的月数 n，n 可以是任意整数。返回计算后的新日期

结果：

select floor(to_date('2018-05-03 20:00:00','yyyy-mm-dd hh24:mi:ss')-to_date('2018-05-01 12:00:00','yyyy-mm-dd hh24:mi:ss')) A from dual;

select sysdate,sysdate+numtoDSinterval(3,'hour') from dual;

13、TO_DSINTERVAL 函数: 可以对日期随意加减，如下例: 对原来的时间加一天。参数格式为 天数 时，分，秒

select sysdate,TO_DSINTERVAL(’01 00:00:00′)+sysdate from dual;

结果：

14、NUMTOYMINTERVAL(n,char_expr)函数：将数字 n 转换为 INTERVAL YEAR TO MONTH 格式，char_expr 可以是 year 或者 month。【YM 表示 YEAR——>MONTH】

select sysdate,sysdate+numtoyminterval(3,’year’) from dual;

结果：

15、TO_YMINTERVAL(char)函数：参数要求格式必须是 ’xx-xx’. 例如 ’02-08′  就表示某两个时间差了 2 年零 8 个月.

 select sysdate,sysdate + to_yminterval(’02-08′) from dual; 

结果：

16、Last_day(d)函数: 返回包含了日期参数的月份的最后一天的日期。是处理月份天数不定的办法，可以用来计算当月中剩余天数。

select to_char(add_months(last_day(sysdate)+1,-1),'yyyy-mm-dd'),last_day(sysdate) from dual;

结果：

17、months_between(date1,date2)函数：计算 date1 和 date2 之间相差的月数. 如果 date1<date2，则返回负数；如果 date1,date2 这两个日期中日分量信息是相同的, 或者这两个日期都分别是所在月的最后一天, 那么返回的结果是一个整数, 否则包括一个小数, 小数为富余天数除以 31，Oracle 以每月 31 天为准计算结果。

select months_between(to_date('2018-02-11','yyyy-mm-dd'),to_date('2018-05-11','yyyy-mm-dd'))"month" from dual;

19、EXTRACT(fmt FROM d)函数：提取日期中的特定部分。fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY 可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。

结果：

20、ROUND(d[,fmt])函数：将日期 d 按照由 fmt 指定的格式进行四舍五入处理. 如果没有给 fmt 则使用缺省设置 ’DD’.

  ① 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。
  ② 如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。
  ③ 默认为“DD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。

  ④ 如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。

SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;

结果：

21、TRUNC(d[,format]): 截断日期时间数据，计算截尾到由 format 指定单位的日期 d. 缺省参数同 ROUNG(d[,fmt]).

select sysdate,trunc(sysdate),trunc(sysdate,'year') YEAR,to_char(trunc(sysdate,'hh'),'hh24')Hours  from dual;

结果：

结果：

22、组合用法：

方式一：使用 TO_CHAR(SYSDATE,’D’)可以求得当前日期是一周的第几天，得到的结果是星期日开始作为第 1 天的，那么星期一就是第 2 天，星期日就是第 8 天。

方式二：SUBSTR 代替 DECODE 函数，NEXT_DAY 函数可以指定当前日期的下一个星期几的日期，比如：今天是 2018-5-14 日，星期五，那么 Next_Day(sysdate,’ 星期一 ’), 得到的日期就是 5 -21，那么这个星期一的日期就是 5 -21 减去 7 天，而星期日的日期就是 5 -21 减去 1 天.

结果：

22.2、查找 2018-05-14 至 2018-05-01 间除星期一和七的天数

结果：

22.3、找出今年的天数：

select add_months(trunc(sysdate,’year’),12)-trunc(sysdate,’year’) from dual;

结果：365

闰年的处理方法：
select to_char(last_day(to_date(‘2018’||’02’,’yyyymm’)),’dd’) from dual;    

如果是 28 就不是闰年 

22.4、五秒钟一个间隔：

结果：

22.5、查找月的第一天, 最后一天

结果：

22.6、查询某周的第一天，例如 2018 年第二周。

方法一：

方法二：

select trunc(to_date(substr('2018-2',1,5)||to_char((to_number(substr('2018-2',6)))*7),'yyyy-ddd'),'d')-6 first_day from dual;

  方法三：

select min(v_date) first_day
from (select (to_date(‘201801’, ‘yyyymm’) + rownum-1) v_date
from all_tables
where rownum < 370)
where to_char(v_date, ‘yyyy-iw’) = ‘2018-02’;

结果：

22.7、查询某周的最后一天

方法一：

方法二：

select trunc(to_date(substr('2018-02',1,5)||to_char((to_number(substr('2018-02',6)))*7),'yyyy-ddd'),'d') last_day from dual

方法三：

select max(v_date) last_day
from (select (to_date(‘201801’, ‘yyyymm’) + rownum-1) v_date
from all_tables
where rownum < 370)
where to_char(v_date, ‘yyyy-iw’) = ‘2018-02’;

22.8、查询某周的日期

一、

二、

结果：

：