MySQL only within the current month?

Question

I have the following MySQL query, and I'm trying to adjust it so it only fetches results which are within the current month (of the current year), I'm guessing you may require more info about my MySQL structure so here it goes - I have a UNIX timestamp generated by PHP's time() stored within the time column (under the referrals table), so with the below setup it would be t2.time.

So my problem is I'm unsure how to proceed, I'm guessing it would be something like adding the following to end of the WHERE clause? => AND t2.time IS WITHIN THE CURRENT MONTH (caps are just on to distinguish problem from rest of query) but i'm not sure how to check if its within the current month.

The MySQL Query:

SELECT t1.username,
       t1.website,
       SUM(IF(t2.type = 'in', 1, 0))  AS in_count,
       SUM(IF(t2.type = 'out', 1, 0)) AS out_count
FROM   users AS t1
       JOIN referrals AS t2
         ON t1.username = t2.author
WHERE  t1.website != ''
GROUP  BY t1.username,
          t1.website
ORDER  BY in_count DESC 
LIMIT  0, 10 

Appreciate all help! :B

Answer 1

This is probably a lot more straightforward than you are going to expect.

SELECT t1.username,
       t1.website,
       SUM(IF(t2.type = 'in', 1, 0))  AS in_count,
       SUM(IF(t2.type = 'out', 1, 0)) AS out_count
FROM   users AS t1
       JOIN referrals AS t2
         ON t1.username = t2.author
WHERE  t1.website != ''
       AND t2.time >= DATE_SUB( CURRENT_DATE, INTERVAL 1 DAY )
GROUP  BY t1.username,
          t1.website
ORDER  BY in_count DESC 
LIMIT  0, 10 

Answer 2

You need to limit the results by using YEAR() and MONTH() functions. Change the WHERE part of your query like this:

WHERE t1.website != ''
AND YEAR(time) = YEAR(NOW())
AND MONTH(time) = MONTH(NOW())

Answer 3

where t2.time >= extract(YEAR_MONTH from CURRENT_DATE) 
  and t2.time <  extract(YEAR_MONTH from CURRENT_DATE + INTERVAL 1 MONTH)

This assume t2.time is a datetime type. If its an integer unix timestamp, you can convert the upper and lower datetime boundaries we created, by using the UNIX_TIMESTAMP() function.

Answer 4

you can use from_unixtime like

date_format(from_unixtime(t2.`time`), '%Y-%m')=date_format(now(), '%Y-%m')

But I think data type integer is not so suitable for this requirement

I would think using datetime will be more suitable, built an index on this column and this also make the filtering easier, like

t2.`time`>='2011-01-01' and t2.`time`<'2011-02-01'

or

date_format(t2.`time`, '%Y-%m')=date_format(now(), '%Y-%m')

Answer 5

For better performances (--> use index), create an index on your date column and user a "between" in your where clause.

select ... from my_table where my:date_field between concat(YEAR(CURRENT_DATE()),'-',MONTH(CURRENT_DATE()),'-01')
and adddate(concat(YEAR(CURRENT_DATE()),'-',MONTH(CURRENT_DATE()),'-01'), interval 1 month);

Answer 6

Check this MONTH function in MySql.

You can add condition like MONTH( FROM_UNIXTIME( t2.time ) ) = Month(NOW())

Edited after getting help in comments from fireeyedboy.