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 |
+-----------------------------------+------------+
精彩评论