I've got a simple problem, but I'm having a difficult time trying to formulate the proper SQL query to get this done. I've got a table that has multiple dates. Each date has multiple times. The times are in start - end increments that are manually added by the user.
+----+------------------+------------------+
| id | start | end |
+----+------------------+------------------+
| 6 | 2014/03/03 08:30 | 2014/03/03 09:00 |
| 7 | 2014/03/03 09:00 | 2014/03/03 11:15 |
| 8 | 2014/03/03 11:15 | 2014/03/03 11:45 |
| 9 | 2014/03/03 11:45 | 2014/03/03 12:45 |
| 10 | 2014/03/03 12:45 | 2014/03/03 13:15 |
| 11 | 2014/03/04 08:45 | 2014/03/04 09:00 |
| 12 | 2014/03/04 09:00 | 2014/03/04 13:00 |
| 13 | 2014/03/04 13:00 | 2014/03/04 13:30 |
| 14 | 2014/03/05 09:00 | 2014/03/05 09:30 |
| 15 | 2014/03/05 09:30 | 2014/03/05 12:30 |
| 16 | 2014/03/05 12:45 | 2014/03/05 12:45 |
| 19 | 2014/03/06 08:45 | 2014/03/06 09:00 |
| 20 | 2014/03/06 09:15 | 2014/03/06 10:00 |
I'm trying to calculate the difference in hours between the earliest start time and the latest end time per date.
Currently I'm just using a simple unix_timestamp conversion on my rows, however, it's only calculating the difference between the first row found, instead of the difference of all rows for a given date.
select
unix_timestamp(min(start)) - unix_timestamp(max(end)) / 60.0 / 60.0 as hours_difference,
min(start) as started,
min(end) as ended
from
athlete_log
group by
day(start)
The problem (as stated above) is that it only grabs the first row from a given date, such as:
| 6 | 2014/03/03 08:30 | 2014/03/03 09:00 |
But I need the earliest start time and the latest start time from:
| 6 | 2014/03/03 08:30 | 2014/03/03 09:00 |
| 7 | 2014/03/03 09:00 | 2014/03/03 11:15 |
| 8 | 2014/03/03 11:15 | 2014/03/03 11:45 |
| 9 | 2014/03/03 11:45 | 2014/03/03 12:45 |
| 10 | 2014/03/03 12:45 | 2014/03/03 13:15 |
Which should yield:
2014/03/03 8:30 - start
2014/03/03 13:15 - end
5.45 - hours_difference
But of course it's only selecting
2014/03/03 8:30 - start
2014/03/03 9:00 - end
.30 - hours_difference
How can I modify my query to select the min start and max end from a given date?
You've grouping on DAY(), which is probably incorrect. You only show data from March 2014 there, but if your DB actually has data from more months/years than just this, you'll be grouping togher 2014/03/03, 2014/02/03, 2013/02/03, etc.. (different years/months, same day-of-month)
You should be doing GROUP BY DATE(start), so you group on the full YYYY-MM-DD date value.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments