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   gt   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

User · Answer

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   gt   NOW   - INTERVAL 7 DAY    AND p2 nNr   1 ORDER BY    p1 kArtikel DESC LIMIT     100

User · Answer

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  gt   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  gt   and  lt  inequality comparisons to specify a range   WHERE datetimecol  gt   DATE NOW      INTERVAL -1 DAY    AND datetimecol  lt   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  gt   NOW     INTERVAL -7 DAY    AND datetimecol  lt   NOW     INTERVAL  0 DAY   the last seven complete days  not including today    WHERE datetimecol  gt   DATE NOW      INTERVAL -7 DAY    AND datetimecol  lt   DATE NOW      INTERVAL  0 DAY   or past six complete days plus so far today       WHERE datetimecol  gt   DATE NOW      INTERVAL -6 DAY    AND datetimecol  lt   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  gt    2017-11-10 00 00      AND datetimecol  lt    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  gt    2017-11-10 00 00    INTERVAL 0 SECOND     AND datetimecol  lt    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

[mysql] MySQL Select last 7 days

Examples related to mysql

Examples related to sql

Examples related to where-clause

Examples related to date-arithmetic