I have a table with number of page views per day. Something like this:
+------+------------+------+----------+
| id | date | hits | mangaID |
+------+------------+------+----------+
| 4876 | 1331843400 | 132 | 13 |
+------+------------+------+----------+
| 4876 | 1331929800 | 24 | 236 |
+------+------------+------+----------+
| 7653 | 1331929800 | 324 | 13 |
+------+------------+------+----------+
I'm trying to get sum hits from last week with the below code:
SELECT sum(hits) as hits FROM om_manga_views WHERE DATE_SUB(CURDATE(),INTERVAL 1 week) <= date and mangaID = '13'
My problem is that I'm storing date as time using strtotime in date's field as int type.
So how can i get what i want!?
Try this:
select sum(hits) hitCount from t
where from_unixtime(date) >= current_date() - interval 1 week and mangaId = 11
Here is the fiddle to play with.
I slightly changed your data because the records you provided are older than 7 days, so the sum would return 0.