select avg(time1) as Avetime from ( select task_no, to_number(substr(to_char((max(to_timestamp(editedate||editetime,'yyyymmddhh24missff')) - min(to_timestamp(editedate||editetime,'yyyymmddhh24missff'))) * 24 * 3600 * 1000),2,9)) as time1 from Platform_TransLogDetail group by task_no )
是时分秒毫秒,我尝试着用下面的函数转 但是会出错,ORA-01830: 日期格式图片在转换整个输入字符串之前结束。 麻烦的就是开发存到数据库中的字段是varchar类型的。不知道要怎么转化和计算 select to_timestamp(editetime,'hh24:mi:ss.ff') from platform_translogdetail
把editedate和editetime连接起来在用to_timestamp转换, select to_timestamp(editedate||editetime,'yyyymmddhh24missff') from platform_translogdetail
用to_date可以将字符串时间按照给定格式字符串转换为时间
from ( select task_no,
to_number(substr(to_char((max(to_timestamp(editedate||editetime,'yyyymmddhh24missff'))
- min(to_timestamp(editedate||editetime,'yyyymmddhh24missff'))) * 24 * 3600 * 1000),2,9)) as time1
from Platform_TransLogDetail
group by task_no )
是时分秒毫秒,我尝试着用下面的函数转 但是会出错,ORA-01830: 日期格式图片在转换整个输入字符串之前结束。
麻烦的就是开发存到数据库中的字段是varchar类型的。不知道要怎么转化和计算
select to_timestamp(editetime,'hh24:mi:ss.ff') from platform_translogdetail
select to_timestamp(editedate||editetime,'yyyymmddhh24missff') from platform_translogdetail