How to get rows from MySQL for the last 7 days?

Question

How can I get the rows from table articles for the last 7 days? Each row has a value timestmp where time is set via time(). I've tried this:

SELECT COUNT(*) FROM `articles` WHERE `timestmp`>NOW()-INTERVAL 168 HOUR

It doesn't work for me :(

The table is:

CREATE TABLE `articles` (
`id`        int(11) NOT NULL AUTO_INCREMENT,
`link`          text NOT NULL,
`article_name`  text NOT NULL,
`descript`      text NOT NULL,
`preview`       text NOT NULL,
`content`       text NOT NULL,
`category`      int(11) NOT NULL,
`author`        text NOT NULL,
`keywrds`       text NOT NULL,
`timestmp`      int(11) NOT NULL,
`modified`      int(11) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT (`keywrds`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

The expected output is all the articles for the last 7 days with names, descriptions and so on.

Add sample data to your question and the expected output. I think you can easily adapt a query from the duplicate link I provided. — Tim Biegeleisen, May 05 '18 at 14:40
What are the values being stored in the `timestmp` column? Are they UNIX timestamps? Please provide data. — Tim Biegeleisen, May 05 '18 at 14:51
One more question: Do you want a cutoff of _exactly_ 7 days, or do you want everything from midnight of the day which is exactly 7 days ago? Example: It is 12pm now, do you want from 12pm one week ago, or from midnight (the start of the day) one week ago? — Tim Biegeleisen, May 05 '18 at 14:53

Tim Biegeleisen · Accepted Answer · 2018-05-05T15:19:20.977

2

Your timestmp column should be storing a UNIX timestamp, which is the number of seconds since the start of the UNIX epoch in January 1, 1970. So, if you just want records which happened exactly within the last 7 days, then you may just subtract 7 days (as seconds) from your timestmp column:

SELECT COUNT(*) AS cnt
FROM articles
WHERE timestmp > UNIX_TIMESTAMP() - 7*24*60*60;

If instead, you want records from 7 days ago, including the entire first day, then we need to do more work. In this case, we have to compute midnight on the first day, then convert that to a UNIX timestamp.

SELECT COUNT(*) AS cnt
FROM articles
WHERE timestmp > UNIX_TIMESTAMP(DATE(NOW() - INTERVAL 7 DAY))

edited May 05 '18 at 15:19

answered May 05 '18 at 15:00

Tim Biegeleisen

502,043
27
286
360

to get the array with rows I should use something like this? – Mr. Tom May 05 '18 at 15:09
SELECT * FROM articles WHERE timestmp > UNIX_TIMESTAMP(FROM_UNIXTIME(timestmp) - INTERVAL 7 DAY – Mr. Tom May 05 '18 at 15:09
@Mr.Tom Actually, I erred, because your query can be done easier than this. I also gave you a second option in case you want to include all 7 days from midnight of the first day. – Tim Biegeleisen May 05 '18 at 15:23
Tim, I can't get it. To get the array with the rows, should I use this query without count()? – Mr. Tom May 05 '18 at 15:39
Oh...yes, just use `SELECT *` from your articles table. But that would also give you all columns, in addition to all rows, and maybe you don't want that. If you only want certain columns, then just do `SELECT col1, col2, ...` – Tim Biegeleisen May 05 '18 at 15:41
Tim the last one I know :) I am bad at timesmps in MySQL(( Thanks!) – Mr. Tom May 05 '18 at 15:43

How to get rows from MySQL for the last 7 days?

1 Answers1