Subtract 5 minutes from a date value

Question

I'm currently writing a snippet of code which will look through the database and see if any admins are currently online and show them on the side of the page as being online.

Every time a user browses through a page on my site (when they are logged in) it grabs the current time and updates a column called lastseen.

I have a role column that marks a user from an admin and what I want it to do is query the database for all the admins and check the lastseen column and if that time is within 5 minutes of the current time then output them to a row which I can then take and format later.

This is what I currently tried but nothing came back.

SELECT username, role, lastseen 
FROM database.accounts 
WHERE lastseen >= DATE_SUB(NOW(), INTERVAL 10 MINUTE) AND role='admin'

I am storing lastseen as date('Y-m-d H-i-s') from PHP so it looks like this 2017-02-18 16:07:42 in the database.

Answer 1

What is your datatype for lastseen, Your query seems correct , just try with

SELECT username, role, lastseen FROM database.accounts 
WHERE STR_TO_DATE(lastseen, '%Y-%m-%d %H:%i:%s') >= DATE_SUB(NOW(), INTERVAL 10 MINUTE) AND role='admin'

Also make sure your PHP and MYSQL Server have same timezones