MySQL仅在当前月份内? [英] MySQL only within the current month?

问题描述

我有以下MySQL查询，并且我试图对其进行调整，以便它仅获取当前月(当年)内的结果，我猜测您可能需要有关MySQL结构的更多信息，因此到这里了-我有一个PHP的time()生成的UNIX时间戳记存储在time列(在referrals表下)中，因此使用以下设置，它将是t2.time.

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.

所以我的问题是我不确定如何继续，我想这就像在WHERE子句的末尾添加以下内容一样? => AND t2.time IS WITHIN THE CURRENT MONTH (大写字母用来区分问题和其余查询)，但是我不确定如何在当月内检查它.

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.

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 

感谢所有帮助！ :B

Appreciate all help! :B

推荐答案

您可以使用

you can use from_unixtime like

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

但是我认为数据类型integer不太适合此要求

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

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

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'

或

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

这篇关于MySQL仅在当前月份内?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！