mysql和 oracle计算两个时间之间相差几分钟

最新推荐文章于 2024-08-02 04:06:52 发布

圆滚滚的蓝胖子

最新推荐文章于 2024-08-02 04:06:52 发布

阅读量707

点赞数 1

分类专栏： Oracle

本文链接：https://blog.csdn.net/weixin_44101779/article/details/89187531

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏


 select a.*,e.name workshop,d.code devicecode,b.orderchildno,c.code productcode,c.name productname,f.code moldcode,f.name moldname,
    k.name as CheckStateDsp,j.name as CheckResultDsp,b.quantity as orderquantity,l.name factoryname,g.description shiftname,
		case when a.CheckEndTime is null then null else
		ceil(round(to_number(a.CheckEndTime-a.CheckStartTime)*24*60,2)) end CheckTimeDiff,to_char(a.senttime,'yyyy-mm-dd hh24:mi:ss') senttimedesp,
		to_char(a.checkstarttime,'yyyy-mm-dd hh24:mi:ss') checkstarttimedesp,
		to_char(a.checkendtime,'yyyy-mm-dd hh24:mi:ss') checkendtimedesp
        from  biz_qa_check_first a
    inner join biz_order_child b on a.orderchildid = b.id
    inner join biz_product c on b.productcode = c.code and b.factoryid = c.factoryid
    left join biz_dev_injector d on a.deviceid = d.id
    left join biz_base_workshop e on d.workshop = e.id
    left join biz_mold f on a.moldid = f.id
    left join biz_base_shift g on a.shift = g.CODE and a.factoryid = g.factoryid
    left join v_sys_dict j on a.checkresult = j.code and j.pcode = 'QaCheckResult' and j.type = ''
    left join v_sys_dict k on a.state = k.code and k.pcode = 'CheckStatus' and k.type = ''
    left join biz_base_factory l on l.id = a.factoryid
    where a.state !='csNew'
    and a.senttime >=	to_date('${checkstarttime} 00:00:00','yyyy-mm-dd hh24:mi:ss')
    and a.senttime <= to_date('${checkendtime} 23:59:59','yyyy-mm-dd hh24:mi:ss')
    order by a.senttime DESC,d.id,sequenceno desc


select a.*,e.name workshop,d.code devicecode,b.orderchildno,c.code productcode,c.name productname,f.code moldcode,f.name moldname,
    k.name as CheckStateDsp,j.name as CheckResultDsp,b.quantity as orderquantity,l.name factoryname,g.description shiftname,
    if(a.checkendtime=null,null,ceil(TIMESTAMPDIFF(SECOND,a.CheckStartTime,a.CheckEndTime)/60)) CheckTimeDiff
    ,DATE_FORMAT(a.senttime,'%Y-%m-%d %H:%i:%s') senttimedesp,
    DATE_FORMAT(a.checkstarttime,'%Y-%m-%d %H:%i:%s') checkstarttimedesp,
    DATE_FORMAT(a.checkendtime,'%Y-%m-%d %H:%i:%s') checkendtimedesp
      from  biz_qa_check_first a
    inner join biz_order_child b on a.orderchildid = b.id
    inner join biz_product c on b.productcode = c.code and b.factoryid = c.factoryid
    left join biz_dev_injector d on a.deviceid = d.id
    left join biz_base_workshop e on d.workshop = e.id
    left join biz_mold f on a.moldid = f.id
    left join biz_base_shift g on a.shift = g.CODE and a.factoryid = g.factoryid
    left join v_sys_dict j on a.checkresult = j.code and j.pcode = 'QaCheckResult' and j.type = ''
    left join v_sys_dict k on a.state = k.code and k.pcode = 'CheckStatus' and k.type = ''
    left join biz_base_factory l on l.id = a.factoryid
    where a.state !='csNew'