mysql计算两列时间差和平均值_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 obj = this.getSession().createSQLQuery(sql).list();

List reList = new ArrayList();

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;

分享到：

2011-10-14 11:12

浏览 2447

分类:数据库

评论