MySQL DATE_ADD用法，5天间隔 [英] MySQL DATE_ADD usage, 5 day interval

问题描述

我正尝试在单个查询中选择5天内的订单总金额（$）和发票数量。我似乎不能这样做。目前的查询我在这里...

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

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'

我不完全确定DATE_ADD是正在寻找的正确功能。

I'm not entirely sure DATE_ADD is the right function I'm looking for.

目前我正在... ....

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

我对mysql date函数来说是相当新鲜的，所以也许我错过了通过mysql文档时需要的功能。

I'm fairly new to mysql date functions so perhaps I just missed the function I needed when going through mysql docs.

更新
我已经更新了我的查询...这仍然没有拉出一行，每天有发票。只有从20日起发现符合userId标准的发票才从19日开始发行。

UPDATE I'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.

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

推荐答案

要获取日期范围之间的记录，请使用：

To get records between a date span, use:

WHERE created BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 5 DAY)

这个例子将会在今天和今天之间得到你的记录（假设今天存在，包括时间）。如果你想过去，看看DATE_SUB。

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.

这篇关于MySQL DATE_ADD用法，5天间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！