开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜