Retrieve date ranges from multiple rows I'm struggling to find an optimal way of achieving this without lots of individual queries and PHP, I'm sure there must be an easier way of doing it.
To simplify we have the following table structure:
CREATE TABLE `log` (
  id INT UNSIGNED AUTO_INCREMENT,
  userid INT,
  itemid INT,
  eventname VARCHAR(30),
  timecreated INT
);

timecreated is a unix timestamp, eventname will be one of 2 statuses (assigned or unassigned).
Assuming the following data (I've put actual dates so it's easier to read):




id
userid
itemid
action
timecreated




1
1
1
assigned
2020-01-01


2
1
2
assigned
2020-01-01


3
1
1
unassigned
2020-03-01


4
1
1
assigned
2020-06-01


5
1
1
unassigned
2020-06-30




The output should be:




itemid
start
end




1
2020-01-01
2020-03-01


2
2020-01-01



1
2020-06-01
2020-06-30
Plan A:  Change the schema to have two date columns.  Then have the code INSERT a row when the item is &quot;assigned&quot; and UPDATE the other column when it is &quot;unasigned&quot;.  This essentially eliminates the entire problem.  And it cuts in half the number of rows needed.
Plan B:  Split the effort.  Using the schema you currently have, SELECT ... ORDER BY ... to get the pairs of rows adjacent.  Then have the application code finish the task by remembering the contents of the first row of each pair (&quot;assigned&quot;) just long enough to blend it with the next row (&quot;unassigned&quot;).