mysql query select entries in the last minute not functioning as intended

Question

I'm using the following MYSQL QUERY to select all entries which are 1 minute or less old (yes I know I should be using PDO):

$result = mysql_query("SELECT customname, online FROM users WHERE online > DATE_SUB(now(), INTERVAL 1 MINUTE) ORDER BY online DESC") 
or die(mysql_error());

Here is my table output:

Last Seen

2015-12-14 08:07:36

2015-12-14 08:07:36

2015-12-14 08:07:36

2015-12-14 06:50:30

2015-12-14 06:50:29

2015-12-14 05:41:16

2015-12-14 03:44:00

My issue is it's selecting times outside of the parameters I specified. The entries 2015-12-14 06:50:30, 2015-12-14 06:50:29, 2015-12-14 05:41:16, 2015-12-14 03:44:00 far exceed the one minute INTERVAL.

UPDATE:

I've tried all the suggestions below and the "duplicates" suggestions and I'm still getting the same results. The datatype for online is datetime.

If my query is:

 $result = mysql_query("SELECT online FROM users WHERE online > date_sub(now(), interval 1 minute) ORDER BY online DESC") 
or die(mysql_error());

then my output is:

Last Seen
2015-12-14 22:51:50 (should display)
2015-12-14 22:51:49 (should display)
2015-12-14 22:51:47 (should display)
2015-12-14 22:16:52 (shouldnt display way over a minute ago)
2015-12-14 22:14:24 (shouldnt display way over a minute ago)
2015-12-14 21:40:15 (shouldnt display way over a minute ago)
2015-12-14 21:34:34 (shouldnt display way over a minute ago)
2015-12-14 21:13:21 (shouldnt display way over a minute ago)
2015-12-14 21:07:57 (shouldnt display way over a minute ago)
2015-12-14 20:12:59 (shouldnt display way over a minute ago)
2015-12-14 19:13:53 (shouldnt display way over a minute ago)
2015-12-14 18:55:40 (shouldnt display way over a minute ago)

"yes I know I should be using PDO", you could at least use the non-deprecated `mysqli` if you don't use PDO :p — Thaillie, Dec 14 '15 at 08:17
take a look here http://stackoverflow.com/questions/15633653/mysql-date-subnow-interval-1-day-24-hours-or-weekday you may have to use `DATE(online)` — RST, Dec 14 '15 at 08:28
Possible duplicate of [How to select last 3 minutes' records from MySQL with PHP](http://stackoverflow.com/questions/7553346/how-to-select-last-3-minutes-records-from-mysql-with-php) — Sadikhasan, Dec 14 '15 at 08:33
What data type is `online` in the database? What result do you see if you add the result of `now()` to your query, e.g. `SELECT customname, now(), online FROM users WHERE online > DATE_SUB(now(), INTERVAL 1 MINUTE) ORDER BY online DESC`? — Matt Gibson, Dec 14 '15 at 08:33
the format of online is DATETIME. If i use SELECT customname, now(), online FROM users WHERE online > DATE_SUB(now(), INTERVAL 1 MINUTE) ORDER BY online DESC then I see timestamps which are far older than 1 minute ago. — michelle, Dec 14 '15 at 22:45

score 3 · Accepted Answer · edited Dec 14 '15 at 10:42

3

Use this function in your query to get the required rows that are entered in table one minute below. Hope this is useful:

TIME_TO_SEC(TimeDiff(now(),receivedDate)) < 60

edited Dec 14 '15 at 10:42

BRoebie

374
3
13

answered Dec 14 '15 at 08:24

Venkat Veeravalli

332
3
4

It returned all these values 2015-12-14 22:42:41 2015-12-14 22:42:40 2015-12-14 22:42:38 2015-12-14 22:16:52 2015-12-14 22:14:24 2015-12-14 21:40:15 2015-12-14 21:34:34 2015-12-14 21:13:21 2015-12-14 21:07:57 2015-12-14 20:12:59 2015-12-14 19:13:53 2015-12-14 18:55:40 which still seems wrong – michelle Dec 14 '15 at 22:44
The issue was PHP and MYSQL were using two different timezones. – michelle Dec 15 '15 at 06:11

mysql query select entries in the last minute not functioning as intended

1 Answers1