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 |
timecreatedthat are nearest to each other? What happens when you have two rows with the sametimecreated?timecreatedshould never be the same for assigned/unassigned for a particular item but multiple items could have the same assigned/unassigned dates as each other when multiple items are updated at the same time. I'm wondering whether it's better to just have a row per assignment with a column for assignedtime and a column for unassignedtime, then use logic to decide when a row needs to be updated or inserted.