MySQL 仅在当月内？

MySQL only within the current month?

提问by newbtophp

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 timecolumn (under the referralstable), so with the below setup it would be t2.time.

我有以下 MySQL 查询，我正在尝试调整它，以便它只获取当前月份（当前年份）内的结果，我猜您可能需要有关我的 MySQL 结构的更多信息，所以在这里- 我有一个由 PHP 生成的 UNIX 时间戳time()存储在time列中（referrals表下），因此使用以下设置，它将是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 WHEREclause? => 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.

所以我的问题是我不确定如何继续，我猜这类似于在WHERE条款末尾添加以下内容？=> AND t2.time IS WITHIN THE CURRENT MONTH（大写只是为了区分问题与查询的其余部分）但我不确定如何检查它是否在当月内。

The MySQL Query:

MySQL查询：

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

感谢所有帮助！:乙

回答by Anax

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

您需要使用YEAR()和函数来限制结果。像这样更改查询的部分：MONTH()WHERE

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

回答by ajreal

you can use from_unixtimelike

你可以使用from_unixtime像

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

But I think data type integeris not so suitable for this requirement

但我认为数据类型integer不太适合这个要求

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

我认为使用datetime会更合适，在此列上建立索引，这也使过滤更容易，例如

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

or

或者

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

回答by Ryan Gooler

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 

回答by goat

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.

这假设 t2.time 是日期时间类型。如果它是一个整数 unix 时间戳，您可以使用 UNIX_TIMESTAMP() 函数转换我们创建的日期时间上限和下限。

回答by Cedric Simon

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

为了获得更好的性能（--> 使用索引），请在您的日期列上创建一个索引，并在您的 where 子句中使用“介于”。

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

回答by Sachin Shanbhag

Check this MONTHfunction in MySql.

在 MySql 中检查这个MONTH函数。

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

您可以添加条件，如 MONTH( FROM_UNIXTIME( t2.time ) ) = Month(NOW())

Edited after getting help in comments from fireeyedboy.

在得到fireeyedboy评论帮助后编辑。