mysql 7天自动拒单功能,MySQL Select最近7天

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.

解决方案

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.