Group by month and year in MySQL

Question

Given a table with a timestamp on each row, how would you format the query to fit into this specific json object format.

I am trying to organize a json object into years / months.

json to base the query off:

{
  "2009":["August","July","September"],
  "2010":["January", "February", "October"]
}

Here is the query I have so far -

SELECT
    MONTHNAME(t.summaryDateTime) as month, YEAR(t.summaryDateTime) as year
FROM
    trading_summary t 
GROUP BY MONTH(t.summaryDateTime) DESC";

The query is breaking down because it is (predictably) lumping together the different years.

Solution 1

GROUP BY YEAR(t.summaryDateTime), MONTH(t.summaryDateTime);

is what you want.

Solution 2

GROUP BY DATE_FORMAT(summaryDateTime,'%Y-%m')

Solution 3

I prefer

SELECT
    MONTHNAME(t.summaryDateTime) as month, YEAR(t.summaryDateTime) as year
FROM
    trading_summary t 
GROUP BY EXTRACT(YEAR_MONTH FROM t.summaryDateTime);

Solution 4

I know this is an old question, but the following should work if you don't need the month name at the DB level:

  SELECT EXTRACT(YEAR_MONTH FROM summaryDateTime) summary_year_month 
    FROM trading_summary  
GROUP BY summary_year_month;

See EXTRACT function docs

You will probably find this to be better performing.. and if you are building a JSON object in the application layer, you can do the formatting/ordering as you run through the results.

N.B. I wasn't aware you could add DESC to a GROUP BY clause in MySQL, perhaps you are missing an ORDER BY clause:

  SELECT EXTRACT(YEAR_MONTH FROM summaryDateTime) summary_year_month 
    FROM trading_summary  
GROUP BY summary_year_month
ORDER BY summary_year_month DESC;

Solution 5

SELECT MONTHNAME(t.summaryDateTime) as month, YEAR(t.summaryDateTime) as year
FROM trading_summary t
GROUP BY YEAR(t.summaryDateTime) DESC, MONTH(t.summaryDateTime) DESC

Should use DESC for both YEAR and Month to get correct order.

View more solutions

Comments

[Derek Adair]

although it appears that GROUP BY MONTH(t.summaryDateTime) DESC doesn't order the months properly for some reason...

[OMG Ponies]

+1: Another alternative using DATE_FORMAT: DATE_FORMAT(t.summaryDateTime, '%Y-%m')

[Duncanmoo]

Further on this, FROM_UNIXTIME is needed if you have a UNIX timestamp in your DB DATE_FORMAT( FROM_UNIXTIME(t.summaryDateTime), '%Y-%m' )

[Mifeet]

Welcome to StackOverflow! This is an old and already answered question. We'll be happy if you could contribute to more pressing questions too. You can find tips on to provide good answers here.

[Edgar Ortega]

please note that the output from EXTRACT(YEAR_MONTH FROM summaryDateTime) is 201901

[Arth]

@EdgarOrtega Yep, but that should be enough to generate the desired JSON object in the original question using application logic. Assuming that some form of looping over the query result is already happening, my approach aims to get the minimum required information from the DB as simply and quickly as possible

[Edgar Ortega]

You're right, that should be enough. My comment was only to indicate what the output from that function is like, maybe someone does not like that format.

[Arth]

@EdgarOrtega No worries, thanks, a worthy contribution!

[Henry]

Thanks @OMGPonies , with your syntax I'm able to perform conditional GROUP BY.

[ow3n]

Performance... my test of GROUP BY YEAR(date), MONTH(date) DESC; (~450 ms) and GROUP BY DATE_FORMAT(date,'%Y-%m') (~850 ms) on a InnoDB table with > 300,000 entries showed the former (the marked answer to this question) took ~half the time as the latter.

[ow3n]

See my comment on the answer above... On large-ish tables this method takes twice as long.

[EJoshuaS - Stand with Ukraine]

If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review

[yasodha bitra]

i tried this context and given my observation.i didnt give without tested.