insert visits after 5 minutes only

Question

I'd like to insert visits like this:

id - user - visit - data
1 - 1 - 2 - date
2 - 1 - 3 - date
3 - 1 - 2 - date after 5 minutes from the first (id 1) - only insert if it has pasted more than 5 minutes from the last similar record.

user 1 visited user 2 and 3. The problem is, I'd like to insert visits without repeating them in the first 5 minutes. After this I'd like to insert.

I tried:

INSERT INTO visits (user, visit, data) 
SELECT '1', '2', NOW() WHERE NOT EXISTS (SELECT 1 FROM visits WHERE user = '1' AND visit = '2' AND data >= DATE_SUB(NOW(), INTERVAL 5 MINUTE))

but it is not working. any ideas?

This seems correct [as seen here](https://stackoverflow.com/questions/913841/mysql-conditional-insert) , can you make a sqlfiddle to reproduce the problem? — Udayraj Deshmukh, Mar 12 '18 at 00:32
Possible duplicate of [MySQL Conditional Insert](https://stackoverflow.com/questions/913841/mysql-conditional-insert) — Udayraj Deshmukh, Mar 12 '18 at 00:35

score 2 · Accepted Answer · answered Mar 12 '18 at 01:12

You can express the logic in the insert:

INSERT INTO visits (user, visit, data) 
    SELECT u.user, u.visit, u.data
    FROM (SELECT 1 as user, 2 as visit, NOW() as data) u
    WHERE NOT EXISTS (SELECT 1
                      FROM visits v
                      WHERE v.user = u.user AND v.visit = u.visit AND
                            u.data >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
                     );

This solves the problem for the particular INSERT. And that might be good enough. However, you are relying on the application to ensure data integrity. A trigger would ensure that no inserts or updates take place that violate your rule.

insert visits after 5 minutes only

1 Answers1