CODEWITHSUNDEEP
mysqlsql
Attila Naghi
Attila Naghi

Reputation: 2686

How do I get the last month using a query?

I have a problem to getting the last month in a query : This is my query: 

SELECT 
  i_chelt_pub_val.`agent_id`,
  i_chelt_pub_val.`date`,
  i_chelt_pub_val.`form_val` 
FROM
  i_chelt_pub_val 
WHERE (
    MONTH(i_chelt_pub_val.`date`) = (MONTH(CURRENT_TIMESTAMP) - 1)
  ) 
  AND i_chelt_pub_val.`agent_id` = '253'

It works fine in 2013, now in 2014 I have an issue, because this line of query MONTH(i_chelt_pub_val.date) = (MONTH(CURRENT_TIMESTAMP) - 1) returns me 12 = 1-1 , which is not good. I want in the where clause to be 12 = 12. How can I do that?

Upvotes: 1

Views: 139

Answers (3)

fancyPants
fancyPants

Reputation: 51938

Assuming, that you want all data from the beginning of the last month to the end of the last month, your query has three issues:

  • you will get for example all januaries of all years
  • depending on when you execute the query, you will miss the data from the very beginning of a month to the time when you execute it, for example execution date is 2014-01-22 15:03:00, then you will get data from 2013-12-22 15:03:00 till today, but not from 2013-12-01 00:00:00
  • it might be quite slow, cause MySQL can't use an index on your date column since you're using an index on it

To fix this, query like this:

SELECT 
  i_chelt_pub_val.`agent_id`,
  i_chelt_pub_val.`date`,
  i_chelt_pub_val.`form_val` 
FROM
  i_chelt_pub_val 
WHERE (
    i_chelt_pub_val.`date` BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00') AND DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-31 23:59:59')
  ) 
  AND i_chelt_pub_val.`agent_id` = '253'

Upvotes: 2

PeterRing
PeterRing

Reputation: 1797

SELECT 
  i_chelt_pub_val.`agent_id`,
  i_chelt_pub_val.`date`,
  i_chelt_pub_val.`form_val` 
FROM
  i_chelt_pub_val 
WHERE (
    (
    MONTH(i_chelt_pub_val.`date`) = (MONTH(CURRENT_TIMESTAMP) - 1) 
      AND 
    YEAR(i_chelt_pub_val.`date`) = (YEAR(CURRENT_TIMESTAMP))
    ) 
    OR 
   (MONTH(i_chelt_pub_val.`date`) = 12 
      AND
    MONTH(CURRENT_TIMESTAMP)=1 
      AND 
    YEAR(i_chelt_pub_val.`date`) = (YEAR(CURRENT_TIMESTAMP)-1))
    )
    AND i_chelt_pub_val.`agent_id` = '253'

Upvotes: 0

Sergio Figueiredo
Sergio Figueiredo

Reputation: 2017

try DATE_SUB():

... = MONTH(DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 MONTH));

Upvotes: 2

Related Questions