MySQL Select last 7 days

Question

I read some posts here and seems like nothing special but I can not still select the entries of the last days.

SELECT 
    p1.kArtikel, 
    p1.cName, 
    p1.cKurzBeschreibung, 
    p1.dLetzteAktualisierung, 
    p1.dErstellt, 
    p1.cSeo,
    p2.kartikelpict,
    p2.nNr,
    p2.cPfad
    
FROM 
    tartikel AS p1 WHERE DATE(dErstellt) > (NOW() - INTERVAL 7 DAY)
    
INNER JOIN 
    tartikelpict AS p2 
    ON (p1.kArtikel = p2.kArtikel) WHERE (p2.nNr = 1)

ORDER BY 
    p1.kArtikel DESC

LIMIT
    100;', $connection);

If I add the between today and last 7 days my Code will not output anything.

Reshape

Thats the Post I read and used in my Code. – Reshape

You don't explain what error you get or how this isn't working. Unless this question is improved it will be closed a duplicate question. – Fiske

I dont get any output (no Error, no result) If I add the line with DATE(dErstellt) > (NOW() - INTERVAL 7 DAY) – Reshape

Those column names killed me – Lavonia

Answer 1

The WHERE clause is misplaced, it has to follow the table references and JOIN operations.

Something like this:

 FROM tartikel p1 
 JOIN tartikelpict p2 
   ON p1.kArtikel = p2.kArtikel 
  AND p2.nNr = 1
WHERE p1.dErstellt >= DATE(NOW() - INTERVAL 7 DAY)
ORDER BY p1.kArtikel DESC

---

EDIT (three plus years later)   

The above essentially answers the question "I tried to add a WHERE clause to my query and now the query is returning an error, how do I fix it?"

As to a question about writing a condition that checks a date range of "last 7 days"...

That really depends on interpreting the specification, what the datatype of the column in the table is (DATE or DATETIME) and what data is available... what should be returned.

To summarize: the general approach is to identify a "start" for the date/datetime range, and "end" of that range, and reference those in a query. Let's consider something easier... all rows for "yesterday".

If our column is DATE type. Before we incorporate an expression into a query, we can test it in a simple SELECT

 SELECT DATE(NOW()) + INTERVAL -1 DAY 

and verify the result returned is what we expect. Then we can use that same expression in a WHERE clause, comparing it to a DATE column like this:

 WHERE datecol = DATE(NOW()) + INTERVAL -1 DAY

For a DATETIME or TIMESTAMP column, we can use >= and < inequality comparisons to specify a range

 WHERE datetimecol >= DATE(NOW()) + INTERVAL -1 DAY
   AND datetimecol <  DATE(NOW()) + INTERVAL  0 DAY

For "last 7 days" we need to know if that mean from this point right now, back 7 days ... e.g. the last 7*24 hours , including the time component in the comparison, ...

 WHERE datetimecol >= NOW() + INTERVAL -7 DAY
   AND datetimecol <  NOW() + INTERVAL  0 DAY

the last seven complete days, not including today

 WHERE datetimecol >= DATE(NOW()) + INTERVAL -7 DAY
   AND datetimecol <  DATE(NOW()) + INTERVAL  0 DAY

or past six complete days plus so far today ...

 WHERE datetimecol >= DATE(NOW()) + INTERVAL -6 DAY
   AND datetimecol <  NOW()       + INTERVAL  0 DAY

I recommend testing the expressions on the right side in a SELECT statement, we can use a user-defined variable in place of NOW() for testing, not being tied to what NOW() returns so we can test borders, across week/month/year boundaries, and so on.

SET @clock = '2017-11-17 11:47:47' ;

SELECT DATE(@clock)
     , DATE(@clock) + INTERVAL -7 DAY 
     , @clock + INTERVAL -6 DAY 

Once we have expressions that return values that work for "start" and "end" for our particular use case, what we mean by "last 7 days", we can use those expressions in range comparisons in the WHERE clause.

(Some developers prefer to use the DATE_ADD and DATE_SUB functions in place of the + INTERVAL val DAY/HOUR/MINUTE/MONTH/YEAR syntax.

And MySQL provides some convenient functions for working with DATE, DATETIME and TIMESTAMP datatypes... DATE, LAST_DAY,

Some developers prefer to calculate the start and end in other code, and supply string literals in the SQL query, such that the query submitted to the database is

  WHERE datetimecol >= '2017-11-10 00:00'
    AND datetimecol <  '2017-11-17 00:00'

And that approach works too. (My preference would be to explicitly cast those string literals into DATETIME, either with CAST, CONVERT or just the + INTERVAL trick...

  WHERE datetimecol >= '2017-11-10 00:00' + INTERVAL 0 SECOND
    AND datetimecol <  '2017-11-17 00:00' + INTERVAL 0 SECOND

---

The above all assumes we are storing "dates" in appropriate DATE, DATETIME and/or TIMESTAMP datatypes, and not storing them as strings in variety of formats e.g. 'dd/mm/yyyy', m/d/yyyy, julian dates, or in sporadically non-canonical formats, or as a number of seconds since the beginning of the epoch, this answer would need to be much longer.

Oberhausen

I never knew about the INTERVAL command. Pretty cool tool to add to the toolbox. Thanks ! – Augustusaugy

@JunedAnsari: expanded answer to include discussion of possible interpretations of "last 7 days", and general discussion of the approach to take in writing conditions for a variety of date/datetime ranges. – Oberhausen

what about the " last seven complete days, including today" case?? – Kendallkendell

@AjayMistry: for that case, the approach is the same. Develop expressions that return the start and end for the specified date/datetime range. My first cut at interpret "last seven complete days plus today" is start and end returned by these expressions, first tested in a SELECT statement: SELECT DATE(NOW()) + INTERVAL - 7 DAY AS start_, DATE(NOW() + INTERVAL 1 DAY AS end_. Once I have expressions that satisfy the specification, I'd use those expressions in a WHERE clause in the form t.dt >= start_expr AND t.dt < end_expr. – Oberhausen

You can simply use CURDATE() instead of DATE(NOW()). – Gullah

@Dan: CURRENT_DATE() and CURRENT_DATE are synonyms for CURDATE(), so those are also options. For me, it's simpler to just use NOW() to return a DATETIME. And if we need to lop of the time component, we follow the same pattern we use for any DATETIME, using the the DATE() function, to return DATE datatype. (I think it's easier to avoid using CURDATE() and its synonyms.) – Oberhausen

Answer 2

Since you are using an INNER JOIN you can just put the conditions in the WHERE clause, like this:

SELECT 
    p1.kArtikel, 
    p1.cName, 
    p1.cKurzBeschreibung, 
    p1.dLetzteAktualisierung, 
    p1.dErstellt, 
    p1.cSeo,
    p2.kartikelpict,
    p2.nNr,
    p2.cPfad  
FROM 
    tartikel AS p1 INNER JOIN tartikelpict AS p2 
    ON p1.kArtikel = p2.kArtikel
WHERE
  DATE(dErstellt) > (NOW() - INTERVAL 7 DAY)
  AND p2.nNr = 1
ORDER BY 
  p1.kArtikel DESC
LIMIT
    100;

Toy