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

最新推荐文章于 2024-10-27 17:34:00 发布

圆滚滚的蓝胖子

最新推荐文章于 2024-10-27 17:34:00 发布

阅读量713

点赞数 1

分类专栏： Oracle

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

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文深入探讨了SQL查询语句的高级用法，包括复杂的联接操作、条件筛选及时间戳处理技巧。通过实例，详细解析了如何从多个表中高效获取所需数据，以及如何精确处理日期和时间，为数据库管理和数据分析提供了实用的解决方案。

摘要由CSDN通过智能技术生成


 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'