MySQL DATE_ADD 使用情况，5 天间隔
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2684520/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL DATE_ADD usage, 5 day interval
提问by Webnet
I'm trying to select the order total sum ($) and invoice count over a 5 day period in a single query. I can't seem to get this to happen though. The current query I have is here...
我正在尝试在单个查询中选择 5 天内的订单总金额 ($) 和发票计数。不过，我似乎无法让这种情况发生。我当前的查询在这里...
SELECT
COUNT(id) as invoice_count,
SUM(orderTotal) as orders_sum,
UNIX_TIMESTAMP(created) as created
FROM ids_invoice
WHERE DATE_ADD(created, INTERVAL +1 DAY)
AND userId = 23 LIMIT 5'
I'm not entirely sure DATE_ADD is the right function I'm looking for.
我不完全确定 DATE_ADD 是我正在寻找的正确函数。
Currently I'm getting....
目前我正在......
Array (
[0] => Array (
[invoice_count] => 420
[orders_total] => 97902.90
[created] => 1252596560
)
)
Array (
[0] => Array (
[invoice_count] => 68
[orders_total] => 14193.20
[created] => 1262900809
)
)
I'd like to get something more like...
我想要更像……
Array (
[0] => Array (
[invoice_count] => 18
[orders_total] => 4902.90
[date] => 04-19-2010
)
)
Array (
[0] => Array (
[invoice_count] => 12
[orders_total] => 5193.20
[date] => 04-20-2010
)
)
I'm fairly new to mysql date functions so perhaps I just missed the function I needed when going through mysql docs.
我对 mysql 日期函数还很陌生，所以也许我在浏览 mysql 文档时错过了我需要的函数。
UPDATEI've updated my query... This still does not pull a row for each day that there were invoices for. It's only pulling invoices from the 19th when there are invoices from the 20th that meet the userId criteria.
更新我已经更新了我的查询......这仍然没有为有发票的每一天拉一行。当 20 日的发票符合 userId 标准时，它只会从 19 日开始提取发票。
SELECT
COUNT(id) as invoice_count,
SUM(orderTotal) as orders_sum,
UNIX_TIMESTAMP(created) as created
FROM ids_invoice
WHERE
created BETWEEN "2010-04-19 00:00:00" AND DATE_ADD("2010-04-19 00:00:00", INTERVAL +5 DAY) AND
userId = 17
回答by OMG Ponies
To get records between a date span, use:
要获取日期跨度之间的记录，请使用：
WHERE created BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 5 DAY)
This example will get you records (assuming any exist for today, including time) for between today and days into the future. Look at DATE_SUB if you want to go into the past.
此示例将为您提供今天和未来几天之间的记录（假设今天存在任何记录，包括时间）。如果您想回到过去，请查看 DATE_SUB。
回答by afaaro
If your created column type is int then just try this one
如果你创建的列类型是 int 那么试试这个
created<= UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL 5 DAY))')
回答by a1ex07
WHERE created <= NOW() AND created >=NOW() - INTERVAL 5 DAY
or it would be better to compare just DATE part of datetime:
或者只比较日期时间的 DATE 部分会更好：
WHERE DATE(created) <= date(NOW()) AND
DATE(created) >= DATE(NOW() - INTERVAL 5 DAY)
