开发者

PHP Calculate starttime and end time from a list of mintues with ID's

Sorry to bother again. I have searched tried my best, looked everywhere and still cannot solve the problem. Could you please help? - I would try my best to explain the problem.

Problem: I have a list of times with an ID and I want to loop through those time within an ID to get the start times and end times. This is an example:

   time_id           time_duration

 1   2010-10-15 11:15:00
 1   2010-10-15 11:20:00
 1   2010-10-15 11:25:00
 1   2010-10-15 11:30:00
 2   2010-10-15 11:35:00
 2   2010-10-15 11:40:00
 2   2010-10-15 11:45:00
 2   2010-10-15 11:50:00
 2   2010开发者_运维百科-10-15 11:55:00
 3   2010-10-15 12:00:00
 3   2010-10-15 12:05:00
 4   2010-10-15 12:10:00
 5   2010-10-15 12:15:00
 5   2010-10-15 12:20:00
 5   2010-10-15 12:25:00

So the result of this should echo:

   for time_id 1: "Start 11:15  -  End 11:30"
   for time_id 2: "Start 11:35  -  End 11:55"
   for time_id 3: "Start 12:00  -  End 12:05"
   for time_id 4: "Start 12:10  -  End 12:10"
   for time_id 5: "Start 12:15  -  End 12:25"

Is this possible? Please help. I have been on this since morning. I tried using for loop but i keep getting weird errors.

Thanks Programming Guys! :)


SELECT 
    time_id, 
    MIN(time_duration) AS start_time, 
    MAX(time_duration) AS end_time
FROM 
    abc_table 
GROUP BY 
    time_id;

That should do the job.


try with this. Maybe it can help you. You just need to change a little bit I guess:

 select concat_ws("","for time_id ","",time_id," ",min(time_duration)," - ",max(time_duration)) as result from table1 group by time_id;

it will give you this result:

+---------------------------------------------------------+
| result                                                  |
+---------------------------------------------------------+
| for time_id 1 2010-10-15 11:15:00 - 2010-10-15 11:30:00 |
| for time_id 2 2010-10-15 11:35:00 - 2010-10-15 11:55:00 |
+---------------------------------------------------------+

EDIT: Just add what you need:

mysql> select concat_ws("","for time_id ","",time_id," ",min(TIME_FORMAT(time_duration,'%h:%i:%s'))," - ",max(TIME_FORMAT(time_duration,'%h:%i:%s'))) as result,
    -> DATE_FORMAT(time_duration,'%d-%m-%Y') as date
    -> from table1
    -> group by time_id;


+-----------------------------------+------------+
| result                            | date       |
+-----------------------------------+------------+
| for time_id 1 11:15:00 - 11:30:00 | 15-10-2010 |
| for time_id 2 11:35:00 - 11:55:00 | 15-10-2010 |
+-----------------------------------+------------+
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜