0

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
2
  • So basically you're looking for creating ordered pairs of timecreated that are nearest to each other? What happens when you have two rows with the same timecreated?
    – J.D.
    Feb 16, 2021 at 15:14
  • Yeah, issue there'll be cases where there's no assigned date or there's no unassigned date. timecreated should 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.
    – jamestsymp
    Feb 16, 2021 at 17:52

1 Answer 1

1

Plan A: Change the schema to have two date columns. Then have the code INSERT a row when the item is "assigned" and UPDATE the other column when it is "unasigned". 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 ("assigned") just long enough to blend it with the next row ("unassigned").

1
  • I had a feeling we'd probably need to go Plan A. The log is the original schema which we were hoping to utilise but we can do a separate table with 2 date columns, going forward it actually makes it easier to save data but we'll still need to do Plan B for initial loading of data.
    – jamestsymp
    Feb 18, 2021 at 8:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.