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

do you aware data type `time` does not store year+month details ? which mean your application is going to running into trouble for year 2012 — ajreal, Jan 11 '11 at 07:06
@ajreal I'm not sure I understand you, but the type is INT within the db, and its a UNIX timestamp generated by PHP's time(). — newbtophp, Jan 11 '11 at 07:08
so is not data type `time`?, my bad, in all cost, avoid using reserved keywords as column or table — ajreal, Jan 11 '11 at 07:11
@ajreal no its not data type time, thanks though I guess I can easily change the column name (to avoid reserved keyword problems) - seeing as the application is still in develepment (so not public/live yet). :) — newbtophp, Jan 11 '11 at 07:14

score 67 · Answer 1 · edited Mar 24 '14 at 18:32

67

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())

edited Mar 24 '14 at 18:32

Michel Ayres

5,891
10
63
97

answered Jan 11 '11 at 07:00

Anax

9,122
5
34
68

using this in combination with from_unixtime() worked for me, (to get a previous month's data) – Andrew Aug 26 '15 at 20:10
Bad: Will calculate the MONTH() for each row. Better try a range comparison on the column. – MartianMartian Aug 29 '18 at 17:22
https://stackoverflow.com/questions/11808232/how-do-i-get-the-first-day-of-the-current-month – MartianMartian Aug 29 '18 at 17:22
select * from t.stuff where date between DATE_FORMAT(NOW(),"%Y-%m-01") and LAST_DAY(NOW()); – MartianMartian Aug 29 '18 at 17:28

score 15 · Accepted Answer · edited Mar 24 '14 at 18:32

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')

score 2 · Answer 3 · answered Jan 11 '11 at 06:57

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

score 2 · Answer 4 · answered Jan 11 '11 at 07:26

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.

score 0 · Answer 5 · answered Jun 06 '17 at 01:28

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);

Sachin Shanbhag · Answer 6 · 2011-01-11T07:27:07.497

-1

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.

edited Jan 11 '11 at 07:27

answered Jan 11 '11 at 06:58

Sachin Shanbhag

54,530
11
89
103

Doesn't the MONTH function require the paremeter/argument to be a MySQL date (and not a UNIX Timestamp) - (unless I've misunderstood/misread)? – newbtophp Jan 11 '11 at 07:02
@newbtophp - Yes you are right. I assume that t2.time is a dateTime field in mysql else there is no way of determining month from only time value. – Sachin Shanbhag Jan 11 '11 at 07:12
1

use `MONTH( FROM_UNIXTIME( your_timestamp ) )` – Decent Dabbler Jan 11 '11 at 07:25
... like ajreal mentioned in his answer: http://stackoverflow.com/questions/4654961/mysql-only-within-the-current-month/4655129#4655129 lol. I should have looked at all the answers first. ;-) – Decent Dabbler Jan 11 '11 at 07:27

MySQL only within the current month?

6 Answers6

Linked