mysql 两列时间之差计算

最新推荐文章于 2021-03-02 18:54:11 发布

原创最新推荐文章于 2021-03-02 18:54:11 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文展示了如何使用SQL查询结合时间转换来获取特定时间段内订单房间的相关信息，包括工作ID、房间ID、房间名称、处理时间及总时长。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

String sql=" SELECT w.work_id, w.room_id,rm.name,CAST(sec_to_time(sum(unix_timestamp(statusTime.end_time)-unix_timestamp(statusTime.create_time))) as CHAR )as times"+
                  ",w.number,statusTime.create_time,statusTime.end_time FROM status_time statusTime,workorder w,room rm"
                  +" where statusTime.work_id=w.work_id and rm.id=w.room_id and statusTime.sid='dealing' group by work_id having " +buildSql(startDate, endDate, sid)+" order by times desc limit 5 ";

    List<Object> obj = this.getSession().createSQLQuery(sql).list();
    List<OrderRoomEntity> reList = new ArrayList<OrderRoomEntity>();
    for (int i = 0; i < obj.size(); i++) {
   Object []object=(Object[]) obj.get(i);
   OrderRoomEntity ore=new OrderRoomEntity();
   Integer day=0;
   String time=object[3].toString();
   String times[]=time.split(":");
   Integer hours=new Integer(times[0]);
   Integer mm=new Integer(times[1]);
   Integer ss=new Integer(times[2]);
   Integer s=hours*60*60+mm*60+ss;
   ore.setNum(new Long(s));
   if(hours>24)
   {
    day=hours/24; //天数
    hours=hours%24; //小时
   }
   ore.setTimeString(day+"天"+hours+"小时"+times[1]+"分钟 "+times[2]+" 秒 ");
   System.out.println(ore.getTimeString());
   ore.setRoomId(new Long((object[1].toString())));
   ore.setRoomName(object[2].toString());
   ore.setProjectId(new Long((object[0].toString())));
   ore.setProjectName(object[4].toString());

   reList.add(ore);
  }

  return reList;