Getting results between dates in PHP/MySQL

Question

I am trying to get all rows from a database that was added between 5 and 10 minutes ago.

Here is what I have tried:

$query9 = mysql_query("SELECT * FROM user_actions  WHERE timestamp BETWEEN date_sub(now(), interval 5 minute) AND date_sub(now(), interval 10 minute);"); 

$onlineUsersLast10Mins = mysql_num_rows($query9);

Despite having data in the database from 6,7,8 and 9 mins ago, this script is not picking it up. Where am I going wrong?

for info, dates are stored as a timestamp in the usual format yyyy-mm-dd hh:mm:ss

It seems that the left side of your between interval is greater than the right side. If you swap them , is this working well? — Lajos Arpad
– Lajos Arpad, Commented Sep 15, 2016 at 13:27
I will write an answer where I try to clarify your problem and explain the solution. Bear with me :) — Lajos Arpad
– Lajos Arpad, Commented Sep 15, 2016 at 13:28

The One and Only ChemistryBlob · Accepted Answer · 2016-09-15 13:31:17Z

1

The semicolon at the end of your query BEFORE the last double quote needs to be removed:

$query9 = mysql_query("SELECT * FROM user_actions  WHERE timestamp BETWEEN date_sub(now(), interval 10 minute) AND date_sub(now(), interval 5 minute)");

This should work.

edited Sep 15, 2016 at 13:31

answered Sep 15, 2016 at 13:28

The One and Only ChemistryBlob

7,90211 gold badges41 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shane Over a year ago

Hi, unfortunately that hasn't made a difference...?

The One and Only ChemistryBlob Over a year ago

Hi, made an edit, also your interval needs to have the date that is further back in time first ---> see here stackoverflow.com/questions/9926147/…

Lajos Arpad · Accepted Answer · 2016-09-15 13:34:45Z

Let's take a look at BETWEEN's documentation.

BETWEEN works with an interval, but if you take a look at this official example:

mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;

-> 1, 0

you will see that 2 is between 1 and 3, but 2 is not between 3 and 1. So, if the right side is bigger than the left side, like in the second case where 3 is the left and 1 is the right, then your element will not be between them.

So, 2 is between 1 and 3, but 2 is not between 3 and 1.

Let us take a look at your query:

SELECT * FROM user_actions  WHERE timestamp BETWEEN date_sub(now(), interval 5 minute) AND date_sub(now(), interval 10 minute);

The left side contains the moment earlier than now with 5 minutes and the right side contains the moment earlier than now with 10 minutes. So, the right side is actually earlier (smaller) than the left side. We need to see whether we have values between 10 minutes ago and 5 minutes ago, not the other way around :)

So, this should be the query:

SELECT * FROM user_actions  WHERE timestamp BETWEEN date_sub(now(), interval 10 minute) AND date_sub(now(), interval 5 minute);

Collectives™ on Stack Overflow

Getting results between dates in PHP/MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related