mysql get month number not relative to the year

Question

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

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

I don't understand what you're trying to do. Are you trying to get some kind of aggregate value by month for the previous 3 years? — Jacobm001

Bill Karwin Bill Karwin · Accepted Answer · 2016-08-23T16:27:16

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 |
+--------+

mysql get month number not relative to the year

2 Answers