Search code examples
mysqltimetabletime-tracking

Possible to sort via two time stamps and display same row twice

I'm looking at creating a time table solution. I have a task sheet that looks like

Area 1 item 1 startTime endTime
Area 1 item1 startTime endTime

I wish to create a display where I can view what even is happening next, either endTime or startTime

i.e.

Newcastle reel 16:45 18:45
Newcastle reel2 17:45 19:45

would output

Newcastle reel 16:45
Newcastle reel 17:45
Newcastle reel 18:45
Newcastle reel 19:45

More so, I would like to detect if the time is a startTime or an endTime would I have to enter two rows for each activity (time,area,item, start|end). I can make the interface to the creation of two rows. I just wondered if there was a better solution.

Solution

  • With your existing schema, this query should work:

    SELECT * FROM
((SELECT Area, Item, startTime AS eventTime FROM tasks)
UNION
(SELECT Area, Item, endTime FROM tasks)) AS t
ORDER BY eventTime

    Basically, you're selecting all the start times, then the end times, then sorting them.