MySQL Select过去7天 [英] MySQL Select last 7 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);

如果我在今天和过去7天之间添加，则我的代码将不会输出任何内容.

解决方案

WHERE子句放错了位置，它必须遵循表引用和JOIN操作.

类似这样的东西:

 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

---

编辑(三年后)

上面的内容基本上回答了以下问题:我试图在查询中添加WHERE子句，现在查询返回错误，我该如何解决?"

关于编写条件以检查过去7天"的日期范围的问题...

这实际上取决于解释规范，表中列的数据类型是什么(DATE或DATETIME)以及可用的数据...应返回什么.

总结:一般的方法是为日期/日期时间范围标识一个开始"，然后为该范围标识结束"，并在查询中引用它们.让我们考虑一些简单的事情……昨天"的所有行.

如果我们的列是DATE类型.在将表达式合并到查询中之前，我们可以在一个简单的SELECT中对其进行测试

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

，并验证返回的结果是我们期望的结果.然后，我们可以在WHERE子句中使用相同的表达式，将其与DATE列进行比较，如下所示:

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

对于DATETIME或TIMESTAMP列，我们可以使用>=和<不等式比较来指定范围

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

对于过去7天"，我们需要知道从现在开始，是否意味着7天……例如最近7 * 24小时，包括比较中的时间部分...

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

最近7天，不包括今天

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

或过去六天，直到今天为止...

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

我建议测试SELECT语句右侧的表达式，我们可以使用用户定义的变量代替NOW()进行测试，而不必与NOW()返回的内容绑定在一起，因此我们可以跨边界测试边界周/月/年边界，等等.

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

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

一旦我们有表达式返回的值适用于特定用例的开始"和结束"，即过去7天"，我们就可以在WHERE子句的范围比较中使用这些表达式. /p>

(某些开发人员更喜欢使用DATE_ADD和DATE_SUB函数代替+ INTERVAL val DAY/HOUR/MINUTE/MONTH/YEAR语法.

MySQL提供了一些方便的功能来处理DATE，DATETIME和TIMESTAMP数据类型... DATE，LAST_DAY，

某些开发人员更喜欢计算其他代码的开始和结束，并在SQL查询中提供字符串文字，以使提交给数据库的查询为

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

这种方法也行得通. (我的偏好是使用CAST，CONVERT或仅+ INTERVAL技巧将这些字符串文字显式转换为DATETIME.

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

---

以上全部假设我们以适当的DATE，DATETIME和/或TIMESTAMP数据类型存储日期"，而不以各种格式将它们存储为字符串，例如'dd/mm/yyyy'，m/d/yyyy，朱利安日期，或者以偶发非典型的格式，或者以自纪元开始以来的秒数为单位，该答案将需要更长的时间.

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.

这篇关于MySQL Select过去7天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！