Alejandro Alejandro - 3 months ago 19
MySQL Question

mysql get month number not relative to the year

I need to get results month by month for the last 3 years. This is what I have:

CREATE DEFINER=`root`@`localhost` PROCEDURE `by_year`()
BEGIN
DROP TEMPORARY TABLE IF EXISTS aaa;
SET @year = 2014;
WHILE @year <= YEAR(CURDATE()) DO
CREATE TEMPORARY TABLE IF NOT EXISTS aaa (Año int, Registrados int, Activos int, Compraron int, Recurrentes int);
INSERT INTO aaa
SELECT @year, registrados.count, activos.count, compraron.count, recurrentes.count FROM

# Here is my query to get the data. With a WHERE that limits the creation date by @year. [YEAR(AU.created) <= @year] For example

END WHILE;

SELECT * FROM aaa;
END


It works for years. But I need to get the result by months or even weeks and days.

My problem is if I replace the YEAR function with MONTH or DAY These functions will return a number taken from the date literally (relative to the bigger divider)

For example from

MONTH('2015-11-25')


I get

11


So MONTH('2015-11-25') is equal to MONTH('2016-11-02')

I see two ways... Either I find a function or something like:

MONTH_FROM_THE_BEGGINING_OF_TIMES('2016-11-02')


That similarly to dates handled by TIME, returns the month number in general in this case from year 1 that should be somenthing like (2015 * 12) + 11 = 24191

The other way could be playing around with double WHILE. And then for days triple WHILE. This will produce a complexity of O(n^3) which is not good.

Any ideas are very welcome.

Thank you

Answer

You might like MySQL's EXTRACT() function:

mysql> SELECT EXTRACT(YEAR_MONTH FROM CURDATE()) AS YM;
+--------+
| YM     |
+--------+
| 201608 |
+--------+

If you want days, you might like TO_DAYS():

mysql> SELECT TO_DAYS(CURDATE()) AS days;
+--------+
| days   |
+--------+
| 736564 |
+--------+
Comments