oracle的时间函数
来源:互联网 发布:鹏业算量软件好吗 编辑:程序博客网 时间:2024/05/22 12:45
IT忍者神龟之oracle获取本周、本月、本季度、本年度的第一天和最后一天 (备查)
select trunc(sysdate,'d')+1 from dual;
select trunc(sysdate,'d')+7 from dual;
--本月
select trunc(sysdate,'mm') from dual;
select last_day(trunc(sysdate)) from dual;
--本季
select trunc(sysdate,'Q') from dual;
select add_months(trunc(sysdate,'Q'),3)-1 from dual;
--本年
select trunc(sysdate,'yyyy') from dual;
select add_months(trunc(sysdate,'yyyy'),12)-1 from dual;
-------------------------------
oracle相关时间计算,得到季度第一天、最后一天
--
-- 本季度第一天
SELECT to_char(TRUNC(SYSDATE, 'Q'), 'YYYY-MM-DD') FROM dual;
-- 上个季度最后一天(可以用本季度第一天减去1得到)
SELECT to_char(TRUNC(SYSDATE, 'Q') - 1, 'YYYY-MM-DD') FROM dual;
-- 上个季度第一天(本季度第一天 减 3个月)
SELECT to_char(add_months(TRUNC(SYSDATE, 'Q'), -3), 'YYYY-MM-DD')
FROM dual;
-- 上个季度第一天(本季度减一个月之后的月份的最后一天)
select to_char(last_day(add_months(trunc(sysdate, 'Q'), -1)), 'yyyy-mm-dd')
from dual
--№1: 取得当前日期是本月的第几周
select to_char(sysdate,'YYYYMMDD W HH24:MI:SS') from dual;
select to_char(sysdate, 'W') from dual;
--№2:取得当前日期是一个星期中的第几天,注意星期日是第一天
select sysdate, to_char(sysdate, 'D') from dual;
--类似:
select to_char(sysdate, 'yyyy') from dual; --当前年
select to_char(sysdate, 'Q' ) from dual; --当前季度
select to_char(sysdate, 'mm') from dual; --当前月
select to_char(sysdate, 'dd') from dual; --当前日
--№3:取当前日期是星期几的中文显示:
select to_char(sysdate, 'day') from dual;
--№4:如果一个表在一个date类型的字段上面建立了索引,如何使用
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
--№5: 得到当前的日期
select sysdate from dual;
--№6: 得到当天凌晨0点0分0秒的日期
select to_char(trunc(sysdate), 'yyyymmdd hh:mi:ss' ) from dual;
-- 得到这天的最后一秒
select trunc(sysdate) + 0.99999 from dual;
-- 得到小时的具体数值
select trunc(sysdate) + 1 / 24 from dual;
select trunc(sysdate) + 7 / 24 from dual;
--№7:得到明天凌晨0点0分0秒的日期
select trunc(sysdate + 1) from dual;
select trunc(sysdate) + 1 from dual;
--№8: 本月一日的日期
select trunc(sysdate, 'mm') from dual;
--№9:得到下月一日的日期
select add_months(trunc(sysdate, 'mm'), 1) from dual;
select trunc(add_months(sysdate, 1), 'mm') from dual;
-- №10:返回当前月的最后一天?
select last_day(sysdate) from dual;
select last_day(trunc(sysdate)) from dual;
select trunc(last_day(sysdate)) from dual;
select trunc(add_months(sysdate, 1), 'mm') - 1 from dual;
--№11: 得到一年的第一天
select trunc(sysdate, 'y') from dual
--№11: 得到一年的每一天
select trunc(sysdate, 'yyyy') + rn - 1 date0
from (select rownum rn from all_objects where rownum < 366) t;
--№12:今天是今年的第N天
SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL;
--№13:如何在给现有的日期加上2年
select add_months(sysdate, 24) from dual;
--№14:判断某一日子所在年分是否为润年
select last_day(trunc(sysdate, 'yyyy') + 31) from dual -- 通过一年的第一天加上31天到2月,然后取2月最后一天
select decode(to_char(last_day(trunc(sysdate, 'y') + 31), 'dd'),
'29',
'闰年',
'平年')
from dual;
--№15:判断两年后是否为润年
select decode(to_char(last_day(trunc(add_months(sysdate, 24), 'y') + 31), 'dd'),
'29',
'闰年',
'平年')
from dual;
--№16:得到日期的季度
select trunc(sysdate, 'mi') from dual ;
select to_number(to_char(sysdate,'mm')) from dual;
select ceil(to_number(to_char(sysdate,'mm'))/3) from dual;
select to_char(sysdate, 'Q') from dual;
--No17:返回某个月的最后一天
select to_char(last_day(sysdate), 'dd') from dual
--本周的第一天和最后一天,考虑到中国人习惯是周一为每周的第一天(国际惯例是周日为第一天,oracle也是这样)
select trunc(sysdate,'d')+1,trunc(sysdate,'d')+7 from dual where to_char(sysdate,'day')<>'星期日'
union all
select trunc(sysdate,'d')+1-7,trunc(sysdate,'d')+7-7 from dual where to_char(sysdate,'day')='星期日'
--当前季度的第一天
select to_char(trunc(sysdate,'Q'),'yyyy-mm-dd') from dual;
--当前季度的最后一天
SELECT to_char(add_months(TRUNC(SYSDATE, 'Q'), +3)-1, 'YYYY-MM-DD')
FROM dual;
select to_char(trunc(add_months(last_day(sysdate), -1) + 1), 'yyyy-mm-dd') "本月第一天",
to_char(last_day(sysdate), 'yyyy-mm-dd') "本月最后一天" FROM dual;
Oracle里的两个日期类型的数据相减得到的天数
相差天数
SQL> select to_date('2010-03-02','yyyy-mm-dd')-to_date('2010-02-10','yyyy-mm-dd') from A
--------20----------结果为2月10日到3月2日的天数
SQL> select trunc(to_date('2010-03-02','yyyy-mm-dd'),'mm')- trunc(to_date('2010-02-10','yyyy-mm-dd'),'mm') from A
--------28----------结果为2月到3月的天数(上sql精确到了月份)
SQL> select trunc(to_date('2010-03-02','yyyy-mm-dd'),'yy')- trunc(to_date('2010-02-10','yyyy-mm-dd'),'yy') from A
--------0-----------结果是按年计算
相差月数
SQL> select months_between(trunc(to_date('2010-03-02','yyyy-mm-dd'),'mm') , trunc(to_date('2010-02-10','yyyy-mm-dd'),'mm')) from A
---------1----------结果为月份差(是前日期与后日期差,反过来为-1)
SQL> select months_between(to_date('2010-03-01','yyyy-mm-dd') , to_date('2010-02-01','yyyy-mm-dd')) from A
---------1----------日相同时计算精确
SQL> select months_between(to_date('2010-03-02','yyyy-mm-dd') , to_date('2010-02-01','yyyy-mm-dd')) from A
--------1.03225806451613------日相差1或N天,--是如何计算的???
相差年
SQL> select trunc(months_between(to_date('2010-03-01','yyyy-mm-dd') , to_date('2009-03-01','yyyy-mm-dd'))/12) from A
---------1----------结果为年份差(是前日期与后日期差,反过来为-1)
SQL> select months_between(to_date('2010-03-01','yyyy-mm-dd') , to_date('2009-03-01','yyyy-mm-dd'))/12 from A
---------1----------结果为年份差(是前日期与后日期差,反过来为-1)
SQL> select trunc( months_between(to_date('2010-05-01','yyyy-mm-dd') , to_date('2009-03-01','yyyy-mm-dd'))/12) from A
---------1----------结果为年份差(是前日期与后日期差,反过来为-1)
SQL> select months_between(to_date('2010-05-01','yyyy-mm-dd') , to_date('2009-03-01','yyyy-mm-dd'))/12 dd from es_accu_card_private
---------1.16666666666667----------
Oracle Trunc函数
SQL> select trunc(5.75,-2),trunc(5.75,-1),trunc(5.75), trunc(5.75,0),trunc(5.75,1), trunc(5.75,2),trunc(5.75,3) from A
---------0---0---5---5---5.7---5.75---5.75------
trunc(x,y),其中如果没有指定y,则对x在0位小数进行截断
trunc(x,y),y为负数,则对x在小数点左边的第|y|位处进行截断,将5.57看成05.57,所以得到0值
-----------------------------
Oracle日期函数:过去、现在及日期的差
上几天把Oracle的数值函数整理完了,今天晚上把Oracle的日期函数整理一下。这些东西吧,不能做到全都记住的话,那也必须做到心中有数,知道有它的存在可以避免开发过程中绕弯子。从到到尾捋顺一遍还是有很多收获的。
一、日期算法
1、sysdate、current_date及systimestamp
Oracle使用计算机操作系统的当前日期和时间,是通过sysdate函数实现的,在任何可以使用Oracle函数的其它地方都可以使用sysdate函数,可以将它视为每个表的一个隐藏列或者伪列。
- select sysdate from dual;
- 结果:2013/12/22 19:14:21
current_date 返回的是当前会话的时区的系统日期,我们平时用的都是东八区,如果将当前会话时间修改成东九区试试:
- alter session set time_zone='+09:00';
- select current_date,sysdate from dual
- 结果: 2013/12/24 20:27:33 2013/12/24 19:27:32
通过例子我们也看到它俩的区别了。
还有一个函数systimestapm,它返回的是本机的系统时间(包含微秒和时区),它跟会话的时区无关
- select systimestamp from dual
- 结果: 24-12月-13 07.34.37.042000 下午 +08:00
2、两个日期的差
两个日期可以直接进行加减,返回的数值单位是天,也可以用一个日期加减某个数值代表N天后(前)
比如十天后发工资,那么十天后是那一天呢?
- select sysdate+10 from dual
- 结果: 2014/1/1 19:38:19
再比如今天除夕是2014-01-30,那么距离过年还有哪个多少天呢
- select to_date('2014-01-30','yyyy-MM-dd') -sysdate from dual
- 结果: 38.1794097222222
晕,还有一个多月才能过年呢啊
3、添加月份、减少月份
用的都是add_months
查询一下三个月后、三个月前分别是哪一天:
- select add_months(sysdate,3),add_months(sysdate,-3) from dual
- 结果: 2014/3/22 19:47:18 2013/9/22 19:47:18
添加年份、减少年份就不用说了,直接在数值上乘以12就可以推算N年前、N年后是哪一天
4、greatest和least
这两个函数在介绍数值函数的时候提到过,在数值函数中分别代表取一组数值中的最大值和最小值。
这哥俩在日期函数中也有应用,greatest是从一组日期数据中取距离当前最近的日期,least是在一组日期中选择最早的日期。
- select greatest(to_date('2013-11-11','yyyy-MM-dd'),to_date('2013-12-11','yyyy-MM-dd'),to_date('2014-01-05','yyyy-MM-dd'),to_date('2013-11-11','yyyy-MM-dd')) from dual
- 结果:1 2013/1/5
- select least(to_date('2013-11-11','yyyy-MM-dd'),to_date('2013-12-11','yyyy-MM-dd'),to_date('2014-01-05','yyyy-MM-dd'),to_date('2013-11-11','yyyy-MM-dd')) from dual
- 结果:1 2013/11/11
注意:这里必须用to_date对字符串进行转化,否则会把他们无法判断是否为日期格式,只能当成字符串去处理。容易出现结果错误的情况。
5、next_day 代表从指定日期开始算,下周几是哪一天
- select next_day(to_date('2013-12-23','yyyy-MM-dd'),2) from dual
- 结果:2013/12/30
6、last_day 计算某个月的最后一天是哪天?
- select last_day(sysdate) from dual
- 结果: 2013/12/31 20:46:07
7、months_between 计算两个日期之间相差的月份数,返回结果可能带小数
计算下自己的周岁:
- select floor(months_between(sysdate ,to_date('1990-08-24','yyyy-MM-dd'))/12) from dual
- 结果:23
8、组合日期函数 : 多个日期函数组合使用
举例:2013年10月28日入职新公司,俩月试用期,新员工过了试用期后的每月第一天可以做转正报告。 那10月28日入职的什么时候做转正报告:
- select last_day(add_months(to_date('2013-10-28','yyyy-MM-dd'),2))+1 from dual
- 结果: 2014/1/1
思路:先用add_months计算俩月后是哪一天,再用last_day计算那个月的最后一天,再+1代表下个月的第一天。
9、日期计算中的round 和 trunc
在数值函数一文中也着重介绍了round和trunc两个函数,那块介绍的是他们在数值函数方面的应用。其实在日期函数中这兄弟俩跟数值函数中的作用类似。
我们用一个日期减去另一个日期的时候经常会带有小数,这是因为Oracle中保留了时、分、秒。
有的时候我们不需要时、分、秒,所以就需要用到round和trunc这两个函数了。
round函数作用是:如果某天的时间在中午之前,那么使用round函数会将改天的时间设置为0点(就是午夜),如果为中午之后就设置为次日零点,这也就是时间方面的四舍五入了。
trunc的函数作用:舍去,无论某天处于什么时间,使用trunc函数后都会将改天时间置为当日0点。
- select to_date('2013-12-23','yyyy-MM-dd')-round(sysdate), to_date('2013-12-23','yyyy-MM-dd')-trunc(sysdate) from dual
- 结果:0 1
1、基本使用
to_date 和 to_char两个函数的作用是相反的,但是用法很类似
to_date是将字符串转换为时间格式 to_char是将日期转换为字符串格式
这两个函数我们平时用的实在是太多了,先写俩例子吧,然后再说其他的:
- select to_date('2013-11-11 12:01:14','yyyy-MM-dd HH:MI:SS') FROM DUAL
- 结果: 2013/11/11 12:01:14
- select to_char(sysdate , 'yyyy-MM-dd HH:MI:SS') from dual;
- 结果: 2013-12-22 09:37:48
to_char的时候可以在format参数中插入字符串,以双引号的方式插入:
- select to_char(sysdate , 'yyyy"年"MM"月"dd"日"') from dual;
- 结果: 2013年12月22日
2、最常见的to_char错误
就是MM代表的是月份 MI代表队 是分钟 不要弄混了,由于Oracle中支持的时间很灵活,所以这块弄混了他不会提示你的。
- select to_char(sysdate ,'yyyy-MI-dd hh:mm:ss') from dual
- 结果: 2013-45-22 09:12:15
出现这样的错误就很悲催了,好在分钟是45还能容易发现, 要是1-12之内的,要从结果发现错误也不容易。
3、to_date 不仅可以接受字符串的参数,还可以接受数值类型的参数:
- select to_date(20131222,'yyyy-MM-dd') from dual
- 结果: 2013/12/22
4、new_time 切换时区
new_time(date,'this','other')
三个参数:date代表的是this时区的时间,‘this’代表的是是用三个字母组成的缩写代替,代表当前时区,‘other’也是用三个字母组成的缩写代替,表示其他时区。
举例:查一下‘2014-10-01 12:00:00’这个时间在北京和夏威夷的时间分别是多少
- select to_date('2014-10-01 12:00:00','yyyy-MM-dd hh24:mi:ss'),new_time(to_date('2014-10-01 12:00:00','yyyy-MM-dd hh24:mi:ss'),'EST','HST') FROM DUAL
- 结果: 2014/10/1 12:00:00 2014/10/1 7:00:00
三、EXTRACT
EXTRACT可用来代替to_char函数来选择日期值中的某一部分(如从一个日期中选择月份和天)
- SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
- SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;
- SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;
- 结果:
- 2013
- 12
- 22
注意:EXTRACT抽取年月日部分和抽取时分部分是不同的,抽取时分的时候
- SELECT EXTRACT(hour FROM TIMESTAMP '2013-12-22 22:34:12') FROM DUAL;
- SELECT EXTRACT(minute FROM TIMESTAMP '2013-12-22 22:34:12') FROM DUAL;
- 结果:
- 22
- 34
再说了这个函数的存在有毛用呢?至今没想明白,用to_char完全可以实现它的功能啊
- select to_char(sysdate ,'yyyy') from dual;
- select to_char(sysdate ,'HH') from dual
- 结果:2013
- 10
DATE 在数据类型存储精确到秒的日期和时间,二TIMESTAMP数据类型存储精确到十亿分之一秒的日期
- Oracle的时间函数
- oracle的时间函数
- ORACLE时间函数的使用
- oracle函数 oracle时间函数
- Oracle常用的一些时间处理函数
- Oracle 函数的创建-----处理时间(通话时间)
- Oracle与SqlServer的时间转换函数
- Oracle 日期时间函数的用法
- oracle 的时间日期转换函数
- ORACLE常用的一些时间函数
- oracle SQL里常用的时间函数
- oracle数据库关于时间间隔的函数
- Oracle中与时间有关的函数
- oracle一些和时间有关的函数
- oracle 两个时间函数的探究
- Oracle和Mysqld的时间函数
- mysql和oracle的时间函数
- Oracle计算时间函数(对时间的加减numtodsinterval、numtoyminterval)
- java finalize方法总结、GC执行finalize的过程
- ubantu17.04 编译android 遇到的问题以及解决方法
- Linux下增加Sawp分区
- 服务器更新软件
- jdk版本导致的DB2异常:必需的字符转换器不可用。ERRORCODE=-4220, SQLSTATE=null
- oracle的时间函数
- 美国财政部报告:DLT数据存储引起监管担忧
- EffectivePython:str和bytes,生成器表达式,enumerate
- JCE cannot authenticate the provider BC
- Fedora 11忘记密码的解决办法
- 在windows下切换python2或python3创建scrapy
- Tomcat8.5 不能进入Manager App
- Android-关于传感器你需要知道的
- replace与replaceAll的区别