CODEWITHSUNDEEP
phpmysql
Markus
Markus

Reputation: 15

MySQL: How to query data of last month if this month of the year is January?

I'm having trouble of finding records of last month (DECEMBER) which is considered as month of the year of 2018, if the current month of this year is JANUARY of 2019.

Here is what I have tried so far, but it does not display the records.

 mysql_query("SELECT * FROM tbl_logs WHERE MONTH(date_added) = MONTH(NOW())-1 ");
 //This must display all records of December of last year.

But on some query, it works. Like finding Last year record:

 mysql_query("SELECT * FROM tbl_logs WHERE YEAR(date_added) = YEAR(NOW())-1 ");
 //This displays all records of last year

My output should be able to display all records of last year of December.

Thank you.

Upvotes: 1

Views: 837

Answers (2)

yak27
yak27

Reputation: 94

This might be a bit of a hack and it seems that Sticky Bit has a good answer but you can try this.

$currentDate = date("m", time());
        if($currentDate != 01){
            $currentDate--;
        }else{
            $currentDate = 12;
        }

And then insert $currentDate.

$query = "SELECT * FROM tbl_logs WHERE MONTH(date_added) = MONTH( " . $currentDate . " )";
mysql_query($query );

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

You can first use date_sub to subtract one month, then use month() or year() to extract the month or year.

For last month:

SELECT *
       FROM tbl_logs
       WHERE month(date_added) = month(date_sub(now(), INTERVAL 1 MONTH));

For last year:

SELECT *
       FROM tbl_logs
       WHERE year(date_added) = year(date_sub(now(), INTERVAL 1 MONTH));

But the more efficient way, as it allows the use of indexes is to calculate and use ranges.

For last month:

SELECT *
       FROM tbl_logs
       WHERE date_added >= concat(year(date_sub(now(), INTERVAL 1 MONTH)),
                                  '-',
                                  lpad(month(date_sub(now(), INTERVAL 1 MONTH)), 2, '0'),
                                  '-',
                                  '01')
             AND date_added < concat(year(now()),
                                     '-',
                                     lpad(month(now()), 2, '0'),
                                     '-',
                                     '01');

For last year:

SELECT *
       FROM tbl_logs
       WHERE date_added >= concat(year(now()) - 1,
                                  '-01-01')
             AND date_added < concat(year(now()),
                                     '-01-01');

The index you'd want for that would be on tbl_logs (date_added).

Upvotes: 1

Related Questions