mySQL/PHP query not restricting by date

Question

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?

Tim Biegeleisen · Accepted Answer · 2017-06-30 15:22:20Z

3

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

edited Jun 30, 2017 at 15:22

answered Jun 30, 2017 at 15:15

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Huy Trịnh · Accepted Answer · 2017-06-30 15:14:55Z

2

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

answered Jun 30, 2017 at 15:14

Huy Trịnh

7533 silver badges11 bronze badges

Collectives™ on Stack Overflow

mySQL/PHP query not restricting by date

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related