Question by Compi | 24/04/2016 at 19:59
I have some data sets with a timestamp column in which the date of the record is specified. Now, I would like to determine the number of entries falling into the period of a specific year, month or day.
How can I implement that using MySQL? I only know that I probably have to use COUNT(id) and GROUP BY, but I have no idea how to write such a query.
Computer Expert
COUNT(id) and GROUP BY is a good choice for doing that. In the following example, the date column is "dat".
Here is how to get the number grouped by years:
Here is how to get the number grouped by months:
And here is how to get the number grouped by days:
You can read more about that in this tutorial.
24/04/2016 at 20:29
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:
Here is the query I have so far —
The query is breaking down because it is (predictably) lumping together the different years.
12 Answers 12
is what you want.
Should use DESC for both YEAR and Month to get correct order.
I know this is an old question, but the following should work if you don’t need the month name at the DB level:
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.
Is it possible I make a simple query to count how many records I have in a determined period of time like a Year, month or day, having a TIMESTAMP field, like:
To have a monthly statistic.
13 Answers 13
Check out the date and time functions in MySQL.
Note (primarily, to potential downvoters). Presently, this may not be as efficient as other suggestions. Still, I leave it as an alternative, and a one, too, that can serve in seeing how faster other solutions are. (For you can’t really tell fast from slow until you see the difference.) Also, as time goes on, changes could be made to MySQL’s engine with regard to optimisation so as to make this solution, at some (perhaps, not so distant) point in future, to become quite comparable in efficiency with most others.
EXTRACT(unit FROM date) function is better as less grouping is used and the function return a number value.
Comparison condition when grouping will be faster than DATE_FORMAT function (which return a string value). Try using function|field that return non-string value for SQL comparison condition (WHERE, HAVING, ORDER BY, GROUP BY).
I tried using the ‘WHERE’ statement above, I thought its correct since nobody corrected it but I was wrong; after some searches I found out that this is the right formula for the WHERE statement so the code becomes like this:
If your search is over several years, and you still want to group monthly, I suggest:
version #1:
version #2 (more efficient):
I compared these versions on a big table with 1,357,918 rows (innodb), and the 2nd version appears to have better results.
version1 (average of 10 executes): 1.404 seconds
version2 (average of 10 executes): 0.780 seconds
( SQL_NO_CACHE key added to prevent MySQL from CACHING to queries.)