0

Using an audit trail in MySQL, how would you find records that had a specific value during a certain time frame?

Let's say I want to get the ids of users who were a site admin one week ago (role_id=1).

Given the table:

**USER**
id | role_id | ...

And a revision table:

**TABLE_HISTORY**
table_name | date | id | column_name | column_value

(assume I'm storing create data in there for better or worse and I'm soft deleting)

How would you find people where role_id = 1 one week ago today using only one query? Keep in mind that their role might have changed to 2 after it was one, this means you can't just select where role_id = 1

Something like this maybe?:

SELECT * FROM 
(
 SELECT * 
 WHERE (date < $timestamp AND column_name = 'role_id') 
 LIMIT 1
) 
WHERE ('is_admin'=1)
Sabrina Leggett
  • 9,079
  • 7
  • 47
  • 50
  • Are you stuck with that schema? – Mike Brant Feb 02 '15 at 15:15
  • Nope. Open for suggestions! – Sabrina Leggett Feb 02 '15 at 17:16
  • Well, it seems that you have gone beyond using audit data just for auditing purpose and needing to actually needing to use it for application purpose.s. At the point you may consider adding two timestamps to the user table to track when the role become effective and when it has be deactvivated and possibly having multiple rows per user id in the table. You would get the rows you are interested in by query with comparision of date of interest against those two fields. i.e. `WHERE date_active >= '[date you are checking for]' AND (date_inactive <= '[date]' OR date_inactive IS NULL)` – Mike Brant Feb 02 '15 at 18:37

0 Answers0