Mysql sum records which have tomorrow's date and after tomorrow etc-CodePudding

In my Mysql DB I have a column called 'mydate' with stored unix timestamps.

I need to get the SUM of all records, that:

has the tomorrow's date (the time shouldn't be taken into consideration, only date)
has today 2 days
has today 3 days etc., but max. today 7 days

I did it this way (just an example for 2 upcoming days):

$sqld = "SELECT 
       sum(case when mydate= (CURDATE()   INTERVAL 1 DAY) then 1 else 0 end) AS p1,
       sum(case when mydate= (CURDATE()   INTERVAL 2 DAY) then 1 else 0 end) AS p2

       FROM mytable";
$rsld = $conn->Execute($sqld);
$x1=$rsld->fields['p1'];$x2=$rsld->fields['p2'];

But when I echo the $x1 and $x2, there is only 0 for both. Definitely there are records for tomorrow and after tomorrow in my table.

Am I doing it wrong?

CodePudding user response：

If you are storing the datetimes as unix timestamps then you need to convert to datetime first and then remove the time portion. Try this

select 
sum(case when date(FROM_UNIXTIME(mydate))=DATE_ADD(CURDATE(),INTERVAL 1 DAY) then 1 else 0 end) p1,
sum(case when date(FROM_UNIXTIME(mydate))=DATE_ADD(CURDATE(),INTERVAL 2 DAY) then 1 else 0 end) p2,
sum(case when date(FROM_UNIXTIME(mydate))=DATE_ADD(CURDATE(),INTERVAL 3 DAY) then 1 else 0 end) p3,
sum(case when date(FROM_UNIXTIME(mydate))=DATE_ADD(CURDATE(),INTERVAL 4 DAY) then 1 else 0 end) p4,
sum(case when date(FROM_UNIXTIME(mydate))=DATE_ADD(CURDATE(),INTERVAL 5 DAY) then 1 else 0 end) p5,
sum(case when date(FROM_UNIXTIME(mydate))=DATE_ADD(CURDATE(),INTERVAL 6 DAY) then 1 else 0 end) p6,
sum(case when date(FROM_UNIXTIME(mydate))=DATE_ADD(CURDATE(),INTERVAL 7 DAY) then 1 else 0 end) p7
from mytable 
where date(FROM_UNIXTIME(mydate))>DATE_ADD(CURDATE(),INTERVAL 1 DAY) and date(FROM_UNIXTIME(mydate))<=DATE_ADD(CURDATE(),INTERVAL 7 DAY);

CodePudding user response：

Ok, I think I found an easier solution.

I just define tomorrows and others date as variables:

$date = date("Y-m-d");

$d1 = strtotime(' 1 day', strtotime($date));
$d2 = strtotime(' 2 day', strtotime($date));

and then just simply compare in Mysql:

sum(case when mydate = $d1 then 1 else 0 end) AS p1

CodePudding user response：

It looks like you're actually looking to count the number of rows instead of sum a particular field. You can use WHERE and GROUP BY to make it easier

SELECT mydate, count(id) as total 
FROM mytable 
WHERE STR_TO_DATE(mydate, '%Y-%m-%d') BETWEEN DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND DATE_ADD(CURDATE(),INTERVAL 7 DAY)

This does turn it into multiple rows instead of a single row as your original query.