按日期从DB中选择，但不包括日期、月份和年份

I have found something like:

我发现了如下内容:

SELECT * FROM POSTY WHERE MONTH(data_dodania) = 5 AND YEAR(data_dodania) = 2017

But it's a bit not what I was looking for. And Maybe it doesn't even exist. That's why I have question, is it possible to do something like check data smarter?

但这不是我想要的。也许它根本不存在。这就是为什么我有疑问，是否有可能做一些更智能的检查数据之类的事情?

Like where date=201705 (first year, second month).

如日期=201705(第一年，第二个月)

This one doesn't work I know I tried. But I typed this to show something I'm asking for.

这个不行，我知道我试过了。但我输入这个是为了显示我想要的东西。

Idk, where date='May2017 or something?

Idk，日期在哪里?

2 个解决方案

#1

You can use MySQL's DATE_FORMAT to format the date in whichever way you want. E.g. following query formats dates in YYYYMM format:

您可以使用MySQL的DATE_FORMAT以任何您想要的方式格式化日期。例如:yyyyyymm格式的查询格式:

SELECT DATE_FORMAT(data_dodania, '%Y%m')
FROM POSTY;

Now, if you have date in this format, you can use DATE_FORMAT in WHERE condition to filter the rows, e.g.:

现在，如果您有这种格式的日期，您可以使用DATE_FORMAT来过滤行，例如:

SELECT *
FROM POSTY
WHERE DATE_FORMAT(data_dodania, '%Y%m') = '201705';

Similarly, format to print May2017 is %M%Y (documtation has a list of formats and examples).

同样，2017年5月打印格式为%M%Y(文档列表中有格式和示例)。

#2

The best way to do a query with a date, is to specify a range to look in. This will be able to utilize indexes on the data_dodania field. Using any functions will cause your query to use a ROW SCAN in order to execute the function and then compare values. I highly recommend using ranges:

使用日期执行查询的最佳方法是指定要查找的范围。这将能够在data_dodania字段上使用索引。使用任何函数都会导致查询使用行扫描，以便执行该函数，然后比较值。我强烈推荐使用范围:

SELECT * FROM POSTY WHERE data_dodania >= '2017-05-01' AND data_dodania <= '2017-05-31'

Another way is to use BETWEEN syntax (This is syntactically the same as above):

另一种方法是在语法之间使用(这在语法上与上面相同):

SELECT * FROM POSTY WHERE data_dodania BETWEEN '2017-05-01' AND '2017-06-01'

#1