CODEWITHSUNDEEP
phpmysql
jonmrich
jonmrich

Reputation: 4323

mySQL/PHP query not restricting by date

My mySQL table has a column date_added which uses type TIMESTAMP. In the table, the timestamps looks like this 2017-06-17 20:08:58 when I use a visual editor.

In my PHP, I'm trying to only get the rows that have a date_added since one week ago. So, I'm doing something like this:

$weekago = strtotime("-1 week");
$sql = "SELECT * from myTable where date_added >= $weekago";

$weekago returns an epoch time like: 1498748730

If I do a straight mySQL query like this:

select * from myTable where date_added > 1498748730

In either case, I just get all rows returned (and yes, some of the rows have a date_added from more than one week ago).

Is the issue that the timestamp formats aren't matching?

Upvotes: 1

Views: 30

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521513

Try using this query:

SELECT * FROM myTable WHERE date_added >= NOW() - INTERVAL 1 WEEK

Your current attempt is comparing epoch time in seconds against a date column. This doesn't make any sense. As @HuyTrịnh correctly suggested, you could wrap the date_added in UNIX_TIMESTAMP to convert it to seconds since epoch. But this approach in pure MySQL is not so nice:

SELECT *
FROM myTable
WHERE UNIX_TIMESTAMP(date_added) >= UNIX_TIMESTAMP() - 7*24*60*60

Upvotes: 3

Huy Trịnh
Huy Trịnh

Reputation: 753

Try this:

$weekago = strtotime("-1 week");
$sql = "SELECT * from myTable where UNIX_TIMESTAMP(date_added) >= $weekago";

This will convert date_added to timestamp so 2 parameter will have the same format

Upvotes: 2

Related Questions