oracle函数

 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：从午夜开始过去的秒数 

1. select to_char(sysdate, 'yyyy') 年,

2. to_char(sysdate, 'mm') 月,

3. to_char(sysdate, 'DD') 日,

4. to_char(sysdate, 'HH24') 时,

5. to_char(sysdate, 'MI') 分,

6. to_char(sysdate, 'SS') 秒,

7. to_char(sysdate, 'DAY') 天,

8. to_char(sysdate, 'Q') 第几季度,

9. to_char(sysdate, 'W') 当月第几周,

10. to_char(sysdate, 'WW') 当年第几周,

11. to_char(sysdate, 'D') 当周第几天,

12. to_char(sysdate, 'DDD') 当年第几天

13. 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(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual; 

结果：

 

1.3、求某天是星期几：

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之间的对应关系。

3.1、select to_timestamp('01-5月-18 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM') from dual;

结果：

3.2、date型转成timestamp：

select cast(sysdate as timestamp) date_to_timestamp from dual;

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

Select TO_TIMESTAMP_TZ('2018-05-14','yyyy-mm-dd') from dual;

结果：

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值。

Select from_tz(timestamp '2018-05-14 12:00:00','8:00')from dual;

结果：

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

Select TZ_OFFSET ('EST') from dual;

结果：

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

Select SYS_EXTRACT_UTC(timestamp'2018-05-14 12:00:00 +08:00') from dual;

结果：

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 SYSDATE,add_months(SYSDATE,-2),add_months(sysdate,2) from dual;

结果：

 

11、floor函数：计算两个日期间的天数

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;

结果：

 

12、NumtoDSinterval(n,char_expr)函数：将数字n转换为INTERNAL DAY TO SECOND(内部时间)格式；char_expr是一个字符串，可以是DAY\HOUR\MINUTE或SECOND。【DS表示 DAY——>SECOND】

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;

结果：-3

 

18、NEXT_DAY(d,string)函数：准确来说一个礼拜是从星期日开始到星期六结束的。例如next_day(sysdate,6)是从当前开始下一个星期五。很多的查询条件和统计都需要求得一周的时间段，也就是星期一到星期日的时间段。给出日期d和星期string之后计算下一个星期的日期. String是星期几;当前会话的语言指定了一周中的某一天.返回值的时间分量与d的时间分量是相同的. String的内容可以忽略大小写.

select sysdate,next_day(sysdate,'星期五') next_day 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 小时。

1. SELECT SYSDATE ,

2.  

3. EXTRACT(YEAR FROM SYSDATE) "year",

4.  

5. EXTRACT(MONTH FROM SYSDATE) "month",

6.  

7. EXTRACT(DAY FROM SYSDATE) "day",

8.  

9. EXTRACT(HOUR FROM SYSTIMESTAMP) "hour",

10.  

11. EXTRACT(MINUTE FROM SYSTIMESTAMP) "minute",

12.  

13. EXTRACT(SECOND FROM SYSTIMESTAMP) "second"

14.  

15. FROM dual;

结果：
 

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;

结果：

1. select Days,

2. A,

3. trunc(A * 24) Hours,

4. trunc(A * 24 * 60 - 60 * TRUNC(A * 24)) Minutes,

5. trunc(A * 24 * 60 * 60 - 60 * TRUNC(A * 24 * 60)) Seconds,

6. trunc(A * 24 * 60 * 60 * 100 - 100 * TRUNC(A * 24 * 60 * 60)) mSeconds

7. from (select trunc(sysdate) Days, sysdate - trunc(sysdate) A from dual)

结果：

22、组合用法：

22.1、根据某一天具体日期，查找该日为星期几，并得到星期一和星期日具体日期

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

1. select to_char(sysdate, 'yyyy-mm-dd') 今天,

2. decode(to_char(sysdate, 'D'),

3. '1',

4. '星期日',

5. '2',

6. '星期一',

7. '3',

8. '星期二',

9. '4',

10. '星期三',

11. '5',

12. '星期四',

13. '6',

14. '星期五',

15. '7',

16. '星期六') 星期几,

17. to_char(sysdate - to_number(to_char(sysdate, 'D')) + 2, 'yyyy-mm-dd') 星期一,

18. to_char(sysdate - to_number(to_char(sysdate, 'D')) + 8, 'yyyy-mm-dd') 星期日

19. from dual;

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

1. select to_char(sysdate, 'yyyy-mm-dd') 今天,'星期'||substr('日一二三四五六',to_number(to_char(sysdate,'d')),1) 星期几,

2. to_char(next_day(sysdate,'星期一')-7,'yyyy-mm-dd') 星期一,

3. to_char(next_day(sysdate,'星期一')-1,'yyyy-mm-dd') 星期日

4. from dual;

结果：

 

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

1. select count(*)

2. from (select rownum - 1 rnum

3. from all_objects

4. where rownum <= to_date('2018-05-14', 'yyyy-mm-dd') -

5. to_date('2018-05-01', 'yyyy-mm-dd') + 1)

6. where to_char(to_date('2018-05-01', 'yyyy-mm-dd') + rnum - 1, 'D') not in

7. ('1', '7')

结果：

 

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、五秒钟一个间隔：

1. select to_char(sysdate, 'SSSSS') / 300,

2.        floor(to_char(sysdate, 'SSSSS') / 300),

3.        floor(to_char(sysdate, 'SSSSS') / 300) * 300,

4.        to_date(floor(to_char(sysdate, 'SSSSS') / 300) * 300, 'SSSSS')隔五秒一个间隔

5.   from dual;

结果：

 

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

1. SELECT sysdate,

2. Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,

3. Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,

4. (Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month

5. FROM dual;

结果：

 

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

方法一：

1. select trunc(decode(ww,

2. 53,

3. to_date(yy || '1231', 'yyyymmdd'),

4. to_date(yy || '-' || to_char(ww * 7), 'yyyy-ddd')),

5. 'd') - 6 first_day

6. from (select substr('2018-2', 1, 4) yy, to_number(substr('2018-2', 6)) ww

7. from dual);

方法二：

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;

方法三：

1. select min(v_date) first_day

2. from (select (to_date('201801', 'yyyymm') + rownum-1) v_date

3. from all_tables

4. where rownum < 370)

5. where to_char(v_date, 'yyyy-iw') = '2018-02';

结果：

 

22.7、查询某周的最后一天

方法一：

1. select trunc(decode(ww,

2. 53,

3. to_date(yy || '1231', 'yyyymmdd'),

4. to_date(yy || '-' || to_char(ww * 7), 'yyyy-ddd')),

5. 'd') last_day

6. from (select substr('2018-02', 1, 4) yy, to_number(substr('2018-02', 6)) ww

7. from dual);

方法二：

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

方法三：

1. select max(v_date) last_day

2. from (select (to_date('201801', 'yyyymm') + rownum-1) v_date

3. from all_tables

4. where rownum < 370)

5. where to_char(v_date, 'yyyy-iw') = '2018-02';

 

22.8、查询某周的日期

一、

1. select v_date,to_char( v_date, 'day') day

2. from (select (to_date('201801', 'yyyymm') + rownum - 1) v_date

3. from all_tables

4. where rownum < 370)

5. where to_char(v_date, 'yyyy-ww') = '2018-01';

二、

1. select min_date, to_char(min_date, 'day') day

2. from (select to_date(substr('2018-01', 1, 4) || '001' + rownum - 1,

3. 'yyyyddd') min_date

4. from all_tables

5. where rownum <= decode(mod(to_number(substr('2018-01', 1, 4)), 4),

6. 0,

7. 366,

8. 365)

9. union

10. select to_date(substr('2018-01', 1, 4) - 1 ||

11. decode(mod(to_number(substr('2018-01', 1, 4)) - 1, 4),

12. 0,

13. 359,

14. 358) + rownum,

15. 'yyyyddd') min_date

16. from all_tables

17. where rownum <= 7

18. union

19. select to_date(substr('2018-01', 1, 4) + 1 || '001' + rownum - 1,

20. 'yyyyddd') min_date

21. from all_tables

22. where rownum <= 7)

23. where to_char(min_date, 'yyyy-ww') = '2018-01';

结果：