Select current months records mysql from timestamp column
Use this query may this help you,
Query = "SELECT * FROM <table_name> WHERE MONTH(date_entered) = MONTH(CURDATE())";
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
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 timestampfield
column.
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 timestampfield
column (meaning MySQL will be forced to perform a fullscan)
Here is SQLFiddle demo
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.