CODEWITHSUNDEEP
mysqltimestamp
rodzilla
rodzilla

Reputation: 355

Select current months records mysql from timestamp column

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!

Upvotes: 15

Views: 63338

Answers (12)

Nurullah
Nurullah

Reputation: 102

SELECT 
    *
FROM
    tableName
WHERE
    EXTRACT(YEAR_MONTH FROM columnName) = EXTRACT(YEAR_MONTH FROM CURDATE())

Upvotes: 0

Sandeep Kumar
Sandeep Kumar

Reputation: 1202

Try this one it will work better because of the range. You don't need to calculate month and year for every row. It will slow the process. User range for better performance.

SELECT * FROM table WHERE columnName between DATE_FORMAT(current_date() ,'%Y-%m-01') and current_date();

Upvotes: 0

gvelasquez85
gvelasquez85

Reputation: 521

As of 2020, you can use BETWEEN to handle the query from the very beginning.

SELECT *
FROM [TABLE]
WHERE [DATE_FIELD] 
BETWEEN 
CAST('2020-30-01' AS DATE) AND CAST('2020-10-31' AS DATE);

I know is not the most "automatic" way, but from a SQL perspective it is very friendly and straightforward.

Source https://www.techonthenet.com/mysql/between.php

Upvotes: 0

peterm
peterm

Reputation: 92785

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

Upvotes: 34

MikA
MikA

Reputation: 5542

The query below can benefit from the index and no functions applied to the timestamp field for where clause evaluation.

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());

Upvotes: 1

indago
indago

Reputation: 2101

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

Upvotes: -3

musicin3d
musicin3d

Reputation: 1036

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.

Upvotes: 6

Farhan
Farhan

Reputation: 33

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

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

Upvotes: 1

Foxx
Foxx

Reputation: 21

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

replace text in ' ' with appropriate from your database

Upvotes: 2

Bhaveshkumar Patel
Bhaveshkumar Patel

Reputation: 172

Use this query may this help you,

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

Upvotes: 6

offboard
offboard

Reputation: 269

try this

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

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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

Upvotes: 3

Related Questions