CODEWITHSUNDEEP
phpmysqlsqldatedatetime
Lewis Boyles-White
Lewis Boyles-White

Reputation: 141

Getting SQL data from specific day

I have a module on my website which shows earnings from the last 30 days. My issue is I want all earnings to be categorized by day as opposed to 24 hour periods.

Here is my code: 

$days = array(29,28,27,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1,0);

foreach($days as $day)
{
    $daybefore = $day -1;

   if($day == 0)
{
    $query = "SELECT SUM(earnings)
FROM earnings 
WHERE `timestamp` <= CURRENT_DATE + INTERVAL
  AND `timestamp`  > CURRENT_DATE + INTERVAL -1 DAY";
}
if($day == 1)
{
$query = "SELECT SUM(earnings)
FROM earnings 
WHERE `timestamp` <= CURRENT_DATE + INTERVAL -" . $day . "
  AND `timestamp`  > CURRENT_DATE + INTERVAL -" . $daybefore . " DAY";
}

The issue is that it takes all data in the 24 hour period from when the quires are made. I want data that is:

Today = All data from the start of the day. Today = All data from the start of that day to the end of that day. Etc.

Thanks for any advice.

Upvotes: 0

Views: 254

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

First, you are doing a loop in your code when that is entirely unnecessary. Let SQL do the work! You can use the date() function to get just the date portion of a datetime.

The following query puts this together to get the last 30 complete days of earnings:

$query = "SELECT date(timestamp) as thedate, SUM(earnings)
FROM earnings
where date(timestamp) >= date(CURRENT_DATE) - interval 30
group by date(timestamp)
";

Upvotes: 1

Related Questions