The title might be a bit confusing but I need help with the following aggregation inside a mysql query. I need to be able to create a single row that contain 3 different schedules for each distinct object_id.
I have the following table, lets call it schedules:
|id |object_id|day|opening_at |closing_at |
|:-:|:——-:|:-:|:—————–:|:—————–:|
|1 |428 |1 |1970-01-01 07:30:00|1970-01-01 22:00:00|
|1 |428 |2 |1970-01-01 07:30:00|1970-01-01 22:00:00|
|1 |428 |3 |1970-01-01 07:30:00|1970-01-01 22:00:00|
|1 |428 |4 |1970-01-01 07:30:00|1970-01-01 22:00:00|
|1 |428 |5 |1970-01-01 07:30:00|1970-01-01 22:00:00|
|1 |428 |6 |1970-01-01 08:30:00|1970-01-01 22:00:00|
|1 |428 |7 |1970-01-01 08:30:00|1970-01-01 21:30:00|
Day column:
- 1 – Sunday
- 2-6 – week days
- 7 – Saturday
So for the above example I would like to obtain the following record:
object_id | weekdays_schedule | saturday_schedule | sunday_schedule
428 | 07:30-22:00 | 08:30-21:30 | 07:30-22:00
For example, the following query returns 3 rows, each with a different schedule:
SELECT DISTINCT object_id,
CONCAT(
DATE_FORMAT(opening_at, '%H:%i'),
'-',
DATE_FORMAT(closing_at, '%H:%i')
) AS schedule
FROM schedules
I thought of using group_concat but i can’t seem to create multiple values for the same object_id.