ORACLE毫秒与日期的相互转换 |
您所在的位置:网站首页 › 毫秒数转换成时间怎么转换出来 › ORACLE毫秒与日期的相互转换 |
毫秒转换为日期 ? 1 2 3 SELECT TO_CHAR(1406538765000 / (1000 * 60 * 60 * 24) + TO_DATE( '1970-01-01 08:00:00' , 'YYYY-MM-DD HH:MI:SS' ), 'YYYY-MM-DD HH24:MI:SS' ) AS CDATE FROM DUAL;日期转换毫秒 ? 1 2 3 SELECT TO_NUMBER(TO_DATE( '2014-07-28 17:12:45' , 'YYYY-MM-DD HH24:MI:SS' ) - TO_DATE( '1970-01-01 8:0:0' , 'YYYY-MM-DD HH24:MI:SS' )) * 24 * 60 * 60 * 1000 FROM DUAL;获取系统当前时间 ? 1 select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss' ) from dual;注意:毫秒转换为日期 格式化的时间可以是12小时制和24小时制。
---1.今年(完成+处理中的流程数量) select count(*) from sa_task where skindid='tkProcessInstance' and (sstatusid='tesFinished' or sstatusid='tesExecuting') and to_char(sCreateTime,'yyyy')='2016'
---2.今年(完成+处理中+尚未处理走过环节数量) select count(*) from sa_task t where scurl is not null and to_char(sCreateTime,'yyyy')='2016' and (sstatusid='tesFinished' or sstatusid='tesExecuting' or sstatusid='tesReady')
----3。今年处理时间中间值 select TO_CHAR(trunc(sum(TO_NUMBER(TO_DATE(to_char(sCreateTime,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD HH24:MI:SS') - TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000)/count(*))/ (1000 * 60 * 60 * 24) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') as sCreateTime from sa_task t where scurl is not null and to_char(sCreateTime,'yyyy')='2016' and (sstatusid='tesFinished' or sstatusid='tesExecuting' or sstatusid='tesReady') *************************************************************************
--今年(完成+处理中的流程数量)共: 2929 完成:2486 处理中:443 --今年(完成+处理中+尚未处理走过环节数量) 22858 --今年走过环节处理时间中间值 25时57分31秒
---1.今年(完成+处理中的流程数量) select count(*) from sa_task where skindid='tkProcessInstance' and (sstatusid='tesFinished' or sstatusid='tesExecuting') and to_char(sCreateTime,'yyyy')='2016'
---1.1今年(完成) select count(*) from sa_task where skindid='tkProcessInstance' and sstatusid='tesFinished' and to_char(sCreateTime,'yyyy')='2016' ---1.2今年(处理中) select count(*) from sa_task where skindid='tkProcessInstance' and sstatusid='tesExecuting' and to_char(sCreateTime,'yyyy')='2016'
---2.今年(完成+处理中+尚未处理走过环节数量) select count(*) from sa_task t where scurl is not null and to_char(sCreateTime,'yyyy')='2016' and (sstatusid='tesFinished' or sstatusid='tesExecuting' or sstatusid='tesReady')
----3.1今年处理时间列表 select sActualStartTime , sActualFinishTime, TO_NUMBER(TO_DATE(to_char(sActualFinishTime,'yyyy-mm-dd hh24:mi:ss'), 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(to_char(sActualStartTime,'yyyy-mm-dd hh24:mi:ss'), 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 from sa_task t where scurl is not null and to_char(sCreateTime,'yyyy')='2016' and sstatusid='tesFinished'
----3.2今年处理时间平均值(秒) select trunc(sum(TO_NUMBER(TO_DATE(to_char(sActualFinishTime,'yyyy-mm-dd hh24:mi:ss'), 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(to_char(sActualStartTime,'yyyy-mm-dd hh24:mi:ss'), 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60)/count(*))||'秒' from sa_task t where scurl is not null and to_char(sCreateTime,'yyyy')='2016' and sstatusid='tesFinished'
----秒转换为小时,分钟,秒格式,把93451替换掉 select trunc(93451/3600)||'时' ,trunc(mod(93451,3600)/60)||'分',mod(93451,60)||'秒' from dual ;
|
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |