MySQL 从时间戳列中选择当前月份的记录mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16500527/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select current months records mysql from timestamp column
提问by rodzilla
I have a mysql DB that has a TIMESTAMP field titled date. How can I select all fields where the month is the current month?
我有一个 mysql 数据库，它有一个名为 date 的 TIMESTAMP 字段。如何选择当月为当月的所有字段？
Thanks in advance!
提前致谢！
回答by peterm
UPDATE
更新
A much better index-friendly way to query your data for a range of dates
一种更好的索引友好方式来查询一系列日期的数据
SELECT id, FROM_UNIXTIME(timestampfield) timestamp
FROM table1
WHERE timestampfield >= UNIX_TIMESTAMP(LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH)
AND timestampfield < UNIX_TIMESTAMP(LAST_DAY(CURDATE()) + INTERVAL 1 DAY);
Note:You don't apply any function to your column data, but rather do all necessary calculations on the right side of the conditions (which are constants and are evaluated only once post-execution). This way you allow MySQL to benefit from index(es) that you might have on the timestampfieldcolumn.
注意：您不会对列数据应用任何函数，而是在条件的右侧进行所有必要的计算（它们是常量并且仅在执行后评估一次）。通过这种方式，您可以让 MySQL 受益于您可能在timestampfield列上拥有的索引。
Original answer:
原答案：
SELECT id, FROM_UNIXTIME(timestampfield) timestamp
FROM table1
WHERE MONTH(FROM_UNIXTIME(timestampfield)) = MONTH(CURDATE())
AND YEAR(FROM_UNIXTIME(timestampfield)) = YEAR(CURDATE())
Note:Although this query produces the correct results it effectively invalidates the proper usage of the index(es) that you might have on the timestampfieldcolumn (meaning MySQL will be forced to perform a fullscan)
注意：虽然这个查询产生了正确的结果，但它实际上使列上索引的正确使用无效timestampfield（意味着 MySQL 将被迫执行全扫描）
Here is SQLFiddledemo
这是SQLFiddle演示
回答by Bhavesh Patel
Use this query may this help you,
使用此查询可能会对您有所帮助，
Query = "SELECT * FROM <table_name> WHERE MONTH(date_entered) = MONTH(CURDATE())";
回答by ypercube??
If you want indexes to be used, don't apply any function to the column:
如果要使用索引，请不要对列应用任何函数：
SELECT *
FROM tableX
WHERE `date` >= UNIX_TIMESTAMP((LAST_DAY(NOW())+INTERVAL 1 DAY)-INTERVAL 1 MONTH)
AND `date` < UNIX_TIMESTAMP(LAST_DAY(NOW())+INTERVAL 1 DAY) ;
The functions used can be found in MySQL docs: Date and Time functions
使用的函数可以在 MySQL 文档中找到：日期和时间函数
回答by offboard
try this
尝试这个
SELECT * FROM table WHERE month(data) = EXTRACT(month FROM (NOW()))
回答by Foxx
SELECT 'data of your choice '
FROM 'your table'
WHERE
MONTH'datecolumn'=MONTH(CURRENT_DATE )
replace text in ' ' with appropriate from your database
用您的数据库中的适当内容替换“ ”中的文本
回答by musicin3d
In my opinion, the following is more readable than the accepted answer...
在我看来，以下内容比接受的答案更具可读性......
SELECT id, FROM_UNIXTIME(timestampfield) timestamp
FROM table1
WHERE timestampfield >= DATE_FORMAT(NOW(), '%Y-%m-01')
Note: This would select any records from the next month as well. That usually doesn't matter, because none have been created.
注意：这也会选择下个月的任何记录。这通常无关紧要，因为还没有创建。
回答by Farhan
SELECT [columns]
FROM [the_table]
WHERE MONTH([date_column]) = MONTH(CURDATE())
Replace the text between [] (including the []) with your data.
用您的数据替换 []（包括 []）之间的文本。
回答by MikA
The query below can benefit from the index and no functions applied to the timestamp field for where clause evaluation.
下面的查询可以从索引中受益，并且没有应用于 where 子句评估的时间戳字段的函数。
SELECT *
FROM TableName
WHERE TimestampField >=
(CURDATE() - INTERVAL (DAY(CURDATE())-1) DAY)
AND TimestampField <
LAST_DAY(CURDATE()) + INTERVAL 1 DAY;
If your timestamp field is time part is truncated, go for this one,
如果您的时间戳字段是时间部分被截断，请选择这个，
SELECT *
FROM TableName
WHERE TimestampField BETWEEN
(CURDATE() - INTERVAL (DAY(CURDATE())-1) DAY)
AND
LAST_DAY(CURDATE());
回答by indago
I think in MySQL here is the simplest method which i have tried and works well, you want to select rows where timestampfieldis in this month.
我认为在 MySQL 中这是我尝试过并且效果很好的最简单的方法，您想选择timestampfield本月所在的行。
SELECT * FROM your_table
WHERE MONTH(timestampfield)=MONTH(CURRENT_DATE()) AND
YEAR(timestampfield)=YEAR(CURRENT_DATE());
the above will return all records that the timestampfield is this month in MySQL
以上将返回时间戳字段是本月在 MySQL 中的所有记录
