How Do I Select Specific Rows From Datetime

[pkedpker]

How do I select specific rows from DATETIME based on DATETIME in betweens lets say

 

Today is 2012-11-27

 

I would like to get a chart based on SUM(reward) for each day for a whole week in the past.

By seeing the image below it should show the same amount for each day.

 

I was thinking something like this but it seems to merge certain days together.

 

SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND
date_credited >= (NOW() - INTERVAL 1 DAY) AND date_credited < (NOW() + INTERVAL 1 DAY)
UNION ALL
SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND
date_credited >= (NOW() - INTERVAL 2 DAY) AND date_credited < (NOW() + INTERVAL 2 DAY)
UNION ALL
SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND
date_credited >= (NOW() - INTERVAL 3 DAY) AND date_credited < (NOW() + INTERVAL 3 DAY)
UNION ALL
SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND
date_credited >= (NOW() - INTERVAL 4 DAY) AND date_credited < (NOW() + INTERVAL 4 DAY)
UNION ALL
SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND
date_credited >= (NOW() - INTERVAL 5 DAY) AND date_credited < (NOW() + INTERVAL 5 DAY)
UNION ALL
SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND
date_credited >= (NOW() - INTERVAL 6 DAY) AND date_credited < (NOW() + INTERVAL 6 DAY)
UNION ALL
SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND
date_credited >= (NOW() - INTERVAL 7 DAY) AND date_credited < (NOW() + INTERVAL 7 DAY)

 

Output is

 

• Day Reward
  

• 1 0.43
  

• 2 0.84
  

• 3 1.17
  

• 4 1.38
  

• 5 1.60
  

• 6 2.03
  

• 7 2.58
  

Edited November 28, 2012 by pkedpker

[Muddy_Funster]

You could try something along the lines of

SELECT EXTRACT(DAY FROM date_created) as day_of_month, referrer, sum(reward)
FROM referrers
WHERE date_created BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
GROUP BY day_of_month

don't know that it will work or not though

[Barand]

Use MySQL DATE() function to extract just the date element of the datetime field

 

SELECT DATE(date_credited) as date_credited, SUM(reward) as total_reward
FROM referrers 
WHERE referrer = '{$_SESSION['username']}' 
 AND DATE(date_credited) BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE()
GROUP BY DATE(date_credited)

[pkedpker]

You could try something along the lines of

SELECT EXTRACT(DAY FROM date_created) as day_of_month, referrer, sum(reward)
FROM referrers
WHERE date_created BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
GROUP BY day_of_month

don't know that it will work or not though

 

Very nice thank you works perfect, except has to be date_credited not created but no problem.

 

 

Use MySQL DATE() function to extract just the date element of the datetime field

 

SELECT DATE(date_credited) as date_credited, SUM(reward) as total_reward
FROM referrers
WHERE referrer = '{$_SESSION['username']}'
AND DATE(date_credited) BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE()
GROUP BY DATE(date_credited)

 

Works good too, learned how to use date function thanks