CODEWITHSUNDEEP
phpmysqlsql
Paul
Paul

Reputation: 201

mySQL Select records within past hour

I'm setting up basic cookie tracking and validating that by making sure the cookie is on their computer AND their IP matches the record I stored AND the record was stored within the past hour. I'm getting hung up on selecting the mySQL data from within the past hour.

As it stands, the column in my table is called 'timestamp', and it just contains the full timestamp inserted with NOW(). I checked around and thought I found the right call, but this didn't work:

$q = "SELECT * FROM conversion_data WHERE ip='$ip' AND timestamp < DATEADD(HOUR, -1, CURRENT_TIMESTAMP) ";

Taking out the timestamp call, it all works fine, so it's just that one part.

Thanks!

Upvotes: 2

Views: 6497

Answers (2)

Andreas &#197;gren
Andreas &#197;gren

Reputation: 3929

Your current query will select rows that are older than one hour. Changing the timestamp predicate so that it will fetch rows that have a time that is newer than or equal to, i.e. greater than or equal to, should work.

$q = "SELECT * FROM conversion_data WHERE ip='$ip' AND timestamp >= DATEADD(HOUR, -1, CURRENT_TIMESTAMP) ";

Please note that I'm not sure if dateadd works like this for mysql, apply a relevant function in your case.

Upvotes: 2

samura
samura

Reputation: 4395

Try:

$q = "SELECT * FROM conversion_data WHERE ip='$ip' AND timestamp > DATE_SUB(CURDATE(), INTERVAL 1 HOUR)";

Upvotes: 5

Related Questions