【oracle 截取时间字段】oracle中怎么截取时间字段

置顶开心果汁 2019-09-19 17:26:59

1052

分类专栏：数据开发-oracle

最后发布:2019-09-19 17:26:59首次发布:2019-09-19 16:46:41

本文链接：https://blog.csdn.net/u013421629/article/details/101027605

版权

oracle 如何截取时分秒，年月日，并计数呢

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串

select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年

select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月

select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日

select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时

select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分

select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒

select to_char(GMT_MODIFIED,'yyyy-mm-dd') as date1,to_char(GMT_MODIFIED,'hh24') as hour,to_char(GMT_MODIFIED,'mi') as mins,to_char(GMT_MODIFIED,'ss') as ss,count(1) as num
from KKPIC where to_char(GMT_MODIFIED,'yyyy-mm-dd')>='2019-01-01'
group by to_char(GMT_MODIFIED,'yyyy-mm-dd'),to_char(GMT_MODIFIED,'hh24'),to_char(GMT_MODIFIED,'mi'),to_char(GMT_MODIFIED,'ss')
having  count(1)>4
order by count(1) desc

在这里插入图片描述