Oracle函数 - 日期函数详解

栏目: 数据库 · 发布时间: 4年前

内容简介:Oracle中的时间类型只有date和TIMESTAMP,TIMESTAMP是比date更精确的类型。日期时间函数用于处理时间类型的数据,Oracle以7位数字格式来存放日期数据,包括世纪、年、月、日、小时、分钟、秒,并且默认日期显式格式为“DD-MON-YY”。在Oracle中准确来说一个礼拜是从星期日开始到星期六结束的,其中时间差以天数为单位。SYSDATE:取得当前的日期和时间,类型是DATE.它没有参数.但在分布式SQL语句中使用时,SYSDATE返回本地数据库的日期和时间.SYSTIMESTAMP

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;

结果: Oracle函数 - 日期函数详解

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;

结果: Oracle函数 - 日期函数详解

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; 

结果: Oracle函数 - 日期函数详解

1.3、求某天是星期几:

select to_char(to_date('2018-05-11','yyyy-mm-dd'),'DAY') from dual;

结果: Oracle函数 - 日期函数详解

select to_char(to_date('2018-05-11','yyyy-mm-dd'),'DAY','NLS_DATE_LANGUAGE=American') from dual;

结果: Oracle函数 - 日期函数详解

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;

结果: Oracle函数 - 日期函数详解

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;

结果: Oracle函数 - 日期函数详解

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;

结果: Oracle函数 - 日期函数详解

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;

结果: Oracle函数 - 日期函数详解

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

Select TZ_OFFSET ('EST') from dual;

结果: Oracle函数 - 日期函数详解

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

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

结果: Oracle函数 - 日期函数详解

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;
结果: Oracle函数 - 日期函数详解

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

select SYSDATE,add_months(SYSDATE,-2),add_months(sysdate,2) from dual;

结果: Oracle函数 - 日期函数详解

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;
结果: Oracle函数 - 日期函数详解

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;
结果: Oracle函数 - 日期函数详解

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

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

结果: Oracle函数 - 日期函数详解

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;

结果: Oracle函数 - 日期函数详解

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

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

结果: Oracle函数 - 日期函数详解

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

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

结果: Oracle函数 - 日期函数详解

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;

结果: Oracle函数 - 日期函数详解

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

SELECT SYSDATE ,

EXTRACT(YEAR FROM SYSDATE) "year",

EXTRACT(MONTH FROM SYSDATE) "month",

EXTRACT(DAY FROM SYSDATE) "day",

EXTRACT(HOUR FROM SYSTIMESTAMP) "hour",

EXTRACT(MINUTE FROM SYSTIMESTAMP) "minute",

EXTRACT(SECOND FROM SYSTIMESTAMP) "second"

FROM dual;

结果:

Oracle函数 - 日期函数详解

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;

结果: Oracle函数 - 日期函数详解

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;

结果: Oracle函数 - 日期函数详解

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)

结果: Oracle函数 - 日期函数详解

22、组合用法:

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

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

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

decode(to_char(sysdate, 'D'),

'1',

'星期日',

'2',

'星期一',

'3',

'星期二',

'4',

'星期三',

'5',

'星期四',

'6',

'星期五',

'7',

'星期六') 星期几,

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

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

from dual;

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

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

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

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

from dual;

结果: Oracle函数 - 日期函数详解

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

select count(*)

from (select rownum - 1 rnum

from all_objects

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

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

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

('1', '7')

结果: Oracle函数 - 日期函数详解

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

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

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

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

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

from dual;

结果: Oracle函数 - 日期函数详解

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

SELECT sysdate,

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

Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,

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

FROM dual;

结果:

Oracle函数 - 日期函数详解

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

方法一:

select trunc(decode(ww,

53,

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

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

'd') - 6 first_day

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

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;

方法三:

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';

结果: Oracle函数 - 日期函数详解

22.7、查询某周的最后一天

方法一:

select trunc(decode(ww,

53,

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

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

'd') last_day

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

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

方法三:

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、查询某周的日期

一、

select v_date,to_char( v_date, 'day' ) day
  from (select (to_date( '201801' , 'yyyymm' ) + rownum - 1 ) v_date
          from all_tables
          where rownum < 370 )
  where to_char(v_date, 'yyyy-ww' ) = '2018-01' ;

二、

select min_date, to_char(min_date, 'day' ) day
  from (select to_date(substr( '2018-01' , 1 , 4 ) || '001' + rownum - 1 ,
                        'yyyyddd' ) min_date
          from all_tables
          where rownum <= decode(mod(to_number(substr( '2018-01' , 1 , 4 )), 4 ),
                                0 ,
                                366 ,
                                365 )
        union
        select to_date(substr( '2018-01' , 1 , 4 ) - 1 ||
                        decode(mod(to_number(substr( '2018-01' , 1 , 4 )) - 1 , 4 ),
                              0 ,
                              359 ,
                              358 ) + rownum,
                        'yyyyddd' ) min_date
          from all_tables
          where rownum <= 7
        union
        select to_date(substr( '2018-01' , 1 , 4 ) + 1 || '001' + rownum - 1 ,
                        'yyyyddd' ) min_date
          from all_tables
          where rownum <= 7 )
  where to_char(min_date, 'yyyy-ww' ) = '2018-01' ;

结果: Oracle函数 - 日期函数详解

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址: https://www.linuxidc.com/Linux/2019-01/156516.htm


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

统计思维

统计思维

[美] Allen B. Downey / 金迎 / 人民邮电出版社 / 2015-9 / 49.00元

现实工作中,人们常常需要用数据说话。可是,数据自己不会说话,需要人对它进行分析和挖掘才能找到有价值的信息。概率统计是数据分析的通用语言,是大数据时代预测未来的根基。如果你有编程背景,就能以概率和统计学为工具,将数据转化为有用的信息和知识,让数据说话。本书介绍了如何借助计算而非数学方法,使用Python语言对数据进行统计分析。 通过书中有趣的案例,你可以学到探索性数据分析的整个过程,从数据收集......一起来看看 《统计思维》 这本书的介绍吧!

HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

MD5 加密
MD5 加密

MD5 加密工具

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具