Counting voucher value total since the beginning of the month
I have a table called "vouchers" it contains fields named "voucher_value" and "redeemed_at". redeemed_at is in Unix time开发者_JAVA技巧stamp format (generated by PHP time() function).
So I need get the total of voucher_value since the beginning of the month.
What's the SQL code for that?
Thanks.
I believe that you will find all that you need in the docs for date and time functions.
EDIT:
Mark's answer is perfectly good. If you are chasing last drop of performance, your column type is timestamp (watch for zone and range limitations) and you have an index on it then:
SELECT SUM(voucher_value)
FROM vouchers
WHERE redeemed_at BETWEEN
UNIX_TIMESTAMP(DATE_ADD(LAST_DAY(DATE_SUB(NOW(), interval 1 month)), interval 1 day))
AND
UNIX_TIMESTAMP(DATE_SUB(DATE_ADD(LAST_DAY(NOW()), interval 1 day), interval 1 second))
should be much faster (as it will not have to apply any transformations to redeemed_at
. The expressions are big/complex (and not thoroughly tested and it is for sure possible to write them more simple/shorter), but what is important is that they are constant and evaluated only once per query. Replace NOW()
with parameter to gain two things
- generalization (of course)
- ability to cache results (mysql will NOT cache any queries that involve
NOW()
)
something like:
SELECT SUM(voucher_value)
FROM vouchers
WHERE MONTH(FROM_UNIXTIME(redeemed_at)) = MONTH(CURDATE())
AND YEAR(FROM_UNIXTIME(redeemed_at)) = YEAR(CURDATE())
perhaps
精彩评论