CODEWITHSUNDEEP
phpmysqlsqldatetimestamp
PoscheL
PoscheL

Reputation: 73

Selecting data from a period of full months based on the current date

I recently got the task to write a reporting tool to get several data historically. But I came to a point where I'm stuck. I can't get my data from full months in the past.

Story: The user gets a field where he can type how long he wants to get the history (of passed months) for example: I want the data of the last 3 months. He types his value, sends the data and (should) get an output like this (because we have September right now):

X data in August,
Y data in July,
Z data in June.

My query is:

SELECT * FROM table WHERE (publish_up between date_sub(NOW(), 
INTERVAL ".$history." MONTH)
AND date_sub(now(), INTERVAL " . -- $history . " MONTH))
AND created_by = '$editorID' AND state = 1 AND sectionid in (1,2,3)";

But with this, obviously I always get the data from NOW - 1 month. So the data I'll show the user is wrong, because it starts somewhere in the middle of a month and ends somewhere in the middle of another month. I want the period to begin at the first day of month and to end with the last day of month.

My first idea was, that I could subtract the amount of days that have already passed in the current month from the current date to get the first day of the last month, but I don't know how to do that. Also I don't know how to get the name of that month.

Upvotes: 2

Views: 1230

Answers (4)

Alexander Tobias Bockstaller
Alexander Tobias Bockstaller

Reputation: 4746

You can calculate an arbitrary date based on the current date using PHP functions mktime and strtotime.

So instead of using awkward, unreadable SQL functions I'd rather calculate the desired time period in PHP directly:

$period_end = mktime(0,0,0, date('n'), 1, date('Y')); // first of current month, time 00:00:00
$period_begin = strtotime('-3 months', $period_end);

You could then embed these in your SQL query as follows:

$query = "SELECT * FROM table WHERE publish_up BETWEEN '" .
         date('Y-m-d H:i:s', $period_begin) ."' AND '" .
         date('Y-m-d H:i:s', $period_end) ."' AND " 
         "created_by = '$editorID' AND state = 1 AND sectionid in (1,2,3)";

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Use EXTRACT YEAR_MONTH to easily see if the dates lie in the months desired.

"SELECT MONTHNAME(publish_up) as month, SUM(some_column), ... 
 FROM mytable 
 WHERE extract(year_month from publish_up) BETWEEN
       extract(year_month from (now() - INTERVAL ".$history." MONTH)) AND
       extract(year_month from now())
 AND created_by = '".$editorID."' AND state = 1 AND sectionid in (1,2,3)
 GROUP BY MONTHNAME(publish_up)";

Upvotes: 0

Markus
Markus

Reputation: 693

SELECT * 
FROM   table 
WHERE  create_by = '$editorID' AND 
       state = 1 AND 
       sectionid in (1,2,3) AND
       published_up > DATE_FORMAT(NOW() ,'%Y-%m-01') - INTERVAL ".$history." MONTH

Upvotes: 0

Giles
Giles

Reputation: 1667

This will give you the end of the previous month:

DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 DAY)

For Month Name see here

Upvotes: 1

Related Questions