SUGGESTION #1
You could use the MONTH() function
mysql> select MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
| 3 |
+--------------+
1 row in set (0.03 sec)
mysql> select MONTH('2013-03-17 02:53:47');
+------------------------------+
| MONTH('2013-03-17 02:53:47') |
+------------------------------+
| 3 |
+------------------------------+
1 row in set (0.04 sec)
mysql>
You would have to put the MONTH function into the SQL command you are running.
SUGGESTION #2
You could also try the PHP Date Function
$month = date("m",$date)
Also DATE_FORMAT(date_column, '%c')
to get '3'
as result and DATE_FORMAT(date_column, '%m')
to get '03'
as result.
First, the condition WHERE date_field >= (CURDATE()-INTERVAL 1 MONTH)
will not restrict your results to the current month. It will fetch all dates from 30-31 days ago up to the current date (and to the future, if there are rows with future dates in the table).
It should be:
WHERE date_field >= LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH
AND date_field < LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY
Now, to the main question, to create 28-31 dates, even if the table has not rows for all the dates, you could use a Calendar
table (with all dates, say for years 1900 to 2200) or create them on the fly, with something like this (the days
table can be either a temporary table or you can even make it a derived table, with a somewhat more complicated query than this one):
CREATE TABLE days
( d INT NOT NULL PRIMARY KEY ) ;
INSERT INTO days
VALUES (0), (1), (2), ....
..., (28), (29), (30) ;
SELECT
cal.my_date AS date_field,
COALESCE(t.val, 0) AS val
FROM
( SELECT
s.start_date + INTERVAL (days.d) DAY AS my_date
FROM
( SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH
AS start_date,
LAST_DAY(CURRENT_DATE)
AS end_date
) AS s
JOIN days
ON days.d <= DATEDIFF(s.end_date, s.start_date)
) AS cal
LEFT JOIN my_table AS t
ON t.date_field >= cal.my_date
AND t.date_field < cal.my_date + INTERVAL 1 DAY ;
The above should work for any type of the date_field
column (date, datetime, timestamp). If the date_field
column is of type DATE
, the last join can be simplified to:
LEFT JOIN my_table AS t
ON t.date_field = cal.my_date ;
Best Answer
Create a months table:
and populate it with dates like these:
Then you can get your results with a query like this:
The output would be in the form:
Changing the dates to month names would, in my view, constitute a presentation issue but, if necessary, MySQL offers built-in functions like
MONTHNAME
to do the transformation directly in SQL.Additional information can be found in the MySQL manual:
TIMESTAMPDIFF()
MONTHNAME()
GREATEST()
LEAST()