webnology — 2010-02-08T06:57:38-05:00 — #1
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?
swampboogie — 2010-02-08T07:46:08-05:00 — #2
Which data type is the column date defined as?
webnology — 2010-02-08T08:35:33-05:00 — #3
swampboogie — 2010-02-08T08:41:28-05:00 — #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.
r937 — 2010-02-08T08:42:03-05:00 — #5
WHERE `date` >= UNIX_TIMESTAMP('2010-02-01')
AND `date` < UNIX_TIMESTAMP('2010-03-01')
webnology — 2010-02-08T09:27:13-05:00 — #6
ok, thx. Got it working. I've used the method from r937.
Thx all for helping out. Learned again today