mysql 两列时间之差计算

 

 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;