Getting revenue for specific month and year

Databases
1

Hi all,

I hav a revenue table where I store several prices, and the date of each transaction. Now I need th e revenue for a specific month/year. I have for now, this code, but it gives no result, although there are entries for that specific period.


SELECT SUM(treatment_price + arrangement_price + product_price) as revenue FROM (`revenue`) WHERE `user_id` = '9' AND month(date) = 02 and year(date) = 2010

Can someone guide me to a solution for this?

Kind regards,
Michel

2

Which data type is the column date defined as?

3

Int.

4

The year and month functions expects a datetime value as input. What values do you store, unixtime or something else?

It is better to store dates using the proper type.

5 
WHERE `date` >= UNIX_TIMESTAMP('2010-02-01')
  AND `date`  < UNIX_TIMESTAMP('2010-03-01')

:slight_smile:

6

ok, thx. Got it working. I’ve used the method from r937.

Thx all for helping out. Learned again today :wink: