如何用mysql pdo选择上周（unix时间）的销售总额？

Question

I am trying to get the sum of the sales for the last week / month with php and mysql pdo. The sales date saved in unix timestamp format in database.

This is how I'm doing it right now;

$week = strtotime("+1 Week");
$getsales = $odb -> prepare("SELECT SUM(`amount`)  FROM `sales` WHERE `date` < :date");
$getsales -> execute(array(':date' => $week));

Can someone give me a better way for it ?

Answer 1

You can use UNIX_TIMESTAMP function to convert date into timestamp and use it in the query, e.g.:

SELECT SUM(`amount`)  
FROM `sales` 
WHERE `date` BETWEEN  UNIX_TIMESTAMP(DATE_ADD(NOW() -7 DAYS)) AND UNIX_TIMESTAMP(NOW());

Here's the documentation for UNIX_TIMESTAMP.