Select current months records mysql from timestamp

问题:

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?

Thanks in advance!

回答1:

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 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 poet 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

回答2:

Use this query may this help you,

Query = "SELECT * FROM <table_name> WHERE MONTH(date_entered) = MONTH(CURDATE())";

回答3:

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

回答4:

try this

SELECT * FROM table WHERE month(data) = EXTRACT(month FROM (NOW()))

回答5:

SELECT 'data of your choice '
FROM 'your table'
WHERE
MONTH'datecolumn'=MONTH(CURRENT_DATE )

replace text in ' ' with appropriate from your database

回答6:

SELECT [columns] 
FROM [the_table] 
WHERE MONTH([date_column]) = MONTH(CURDATE())

Replace the text between [] (including the []) with your data.

回答7:

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.

回答8:

I think in MySQL here is the simplest method which i have tried and works well, you want to select rows where timestampfield is in this month.

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