0

hi im trying to get monthly sale of a online shop for a given period of time i'm using unix epoch(time()) for storing sale date in the table

sold table looks like this

+-----------+---------------------+---------+----------+ 
| id        | product_id          | price   | date     |
+-----------+---------------------+---------+----------+
|         1 | 20                  |   2000  |1323299365| 
+-----------+---------------------+---------+----------+
|         2 | 28                  |   3500  |1323297389| 
+-----------+---------------------+---------+----------+

i want to findout monthly sale for a given period of time like

$from="3/2011";
$to  ="4/2011";

i know how to change these dates to unix timestamp and get the result by something like that 

//explode from
//$from = maketime(x,x,x,x,x);
//explode to
//$to= maketime(x,x,x,x,x);
$query = "select * from `sold_table` where date > $from and `date` < $to ";

but what i want is to group the results by month so i can have monthly statics i know this query doesn't work but i want something like this 

$query = "select sum(`price of each month`) from `sold_table` where date > $from and `date` < $to group by month ";

so i can have a result like

march(03) = 25000$;
april(04) = 200$ ;

i can group the result by every 30 days but i think there should be a cleaner way

maybe i can find out month between to epoch time like 1323299365 and 1323297389 and then get the monthly sale for each one of month ? but i dont know ho to extract month between two epoch time

max
  • 3,614
  • 9
  • 59
  • 107
  • There doesn't seem to be a way of extracting date parts like month from Unix timestamps directly. So you need to do two things: 1) convert from Unix timestamps, 2) extract the necessary parts of date for grouping by them. One way of doing the latter is like in Peter Krejci's answer, alternatives can be found here: [MySQL Query GROUP BY day / month / year](http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year). – Andriy M Jan 07 '12 at 20:34

1 Answers1

1

Try something like this:

SELECT SUM(price), DATE_FORMAT(FROM_UNIXTIME(date), "%m") AS month FROM sold_table GROUP BY month

But be aware also of the year, you can use combination of month and year.

Peter Krejci
  • 3,182
  • 6
  • 31
  • 49