CODEWITHSUNDEEP
mysqldateintervals
Ben
Ben

Reputation: 62356

MySQL DATE_ADD usage, 5 day interval

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'

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

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

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

Upvotes: 15

Views: 52611

Answers (3)

afaaro
afaaro

Reputation: 21

If your created column type is int then just try this one

created<= UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL 5 DAY))')

Upvotes: 2

a1ex07
a1ex07

Reputation: 37354

WHERE created <= NOW() AND created >=NOW() - INTERVAL 5 DAY

or it would be better to compare just DATE part of datetime:

WHERE DATE(created) <= date(NOW()) AND 
  DATE(created) >= DATE(NOW() - INTERVAL 5 DAY)

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332521

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.

Upvotes: 25

Related Questions