How to get values ​​for every day in a month

Question

Data:

values date 14 1.1.2010 20 1.1.2010 10 2.1.2010 7 4.1.2010 ... 

An example request in January 2010 should contain 31 lines. One for every day. And the values ​​must be added. Now I can do it with 31 queries, but I would like this to work with one. Is it possible?

results:

 1. 34 2. 10 3. 0 4. 7 ... 

Answer 1

This is actually very difficult to do in SQL. One way to do this is to have a long select statement with UNION ALLs to generate numbers from 1 to 31. This demonstrates the principle, but for clarity, I settled on 4:

 SELECT MonthDate.Date, COALESCE(SUM(`values`), 0) AS Total FROM ( SELECT 1 AS Date UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL -- SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) AS MonthDate LEFT JOIN Table1 AS T1 ON MonthDate.Date = DAY(T1.Date) AND MONTH(T1.Date) = 1 AND YEAR(T1.Date) = 2010 WHERE MonthDate.Date <= DAY(LAST_DAY('2010-01-01')) GROUP BY MonthDate.Date 

It might be better to use a table to store these values ​​and join it instead.

Result:

 1, 34 2, 10 3, 0 4, 7 

Answer 2

Given that for some dates you do not have data, you need to fill in the blanks. One approach to this is to have a calendar table filled with all the necessary dates, and join it. If you want the results to display the day numbers as you show in your question, you can also fill them in your calendar as well as labels.

You will attach your data table field to the calendar table date field, a group for this field, and sum values. You can specify limits for the range of dates covered.

So you can have:

 CREATE TABLE Calendar ( label varchar, cal_date date, primary key ( cal_date ) ) 

Query:

 SELECT c.label, SUM( d.values ) FROM Calendar c JOIN Data_table d ON d.date_field = c.cal_date WHERE c.cal_date BETWEEN '2010-01-01' AND '2010-01-31' GROUP BY d.date_field ORDER BY d.date_field 

Update

I see that you have dates, not dates. You could just use the MySQL DATE () function in the join, but that probably would not be optimal. Another approach would be to have a start and end time in the calendar table that defines a "bucket of time" for each day.

Answer 3

To dynamically retrieve dates in a date range using SQL, you can do this (example in mysql):

Create a table to store numbers from 0 to 9.

 CREATE TABLE ints ( i tinyint(4) ); insert into ints (i) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 

Run the query as follows:

 select ((curdate() - interval 2 year) + interval (ti * 100 + ui * 10 + vi) day) AS Date from ints t join ints u join ints v having Date between '2015-01-01' and '2015-05-01' order by ti, ui, vi 

This will create all dates between January 1, 2015 and May 1, 2015.

 Output 2015-01-01 2015-01-02 2015-01-03 2015-01-04 2015-01-05 2015-01-06 ... 2015-05-01 

The query joins the ints table 3 times and gets an extra number (from 0 to 999). He then adds this number as a daily interval, starting from a specific date, in this case, the date is 2 years ago. Any date range from 2 years ago and 1000 days in advance can be obtained using the above example. To generate a query that generates dates for more than 1000 days, simply join the ints table to allow up to 10,000 days of the range, etc.

Answer 4

This works for me ... Its a modification of the request that I found on another site. The "INTERVAL 1 MONTH" sentence guarantees the receipt of data for the current month, including zeros for several days that have no hits. Change this to “INTERVAL 2 MONTH” to get past month data, etc.

I have a table called "payload" with the "timestamp" column - Im, then connecting the timestamp column to dynamically generated dates, dropping it so that the dates match in the ON clause.

 SELECT `calendarday`,COUNT(P.`timestamp`) AS `cnt` FROM (SELECT @tmpdate := DATE_ADD(@tmpdate, INTERVAL 1 DAY) `calendarday` FROM (SELECT @tmpdate := LAST_DAY(DATE_SUB(CURDATE(),INTERVAL 1 MONTH))) AS `dynamic`, `payload`) AS `calendar` LEFT JOIN `payload` P ON DATE(P.`timestamp`) = `calendarday` GROUP BY `calendarday` 

Answer 5

If I understand correctly a rather vague question, you want to know the number of records for each date for a month. If so, here's how to do it:

 SELECT COUNT(value_column) FROM table WHERE date_column LIKE '2010-01-%' GROUP BY date_column