Query to select specific columns based on current date

Question

I have a table named 'mrtg' that has four columns:

mysql> select * from mrtg;
+----------+------+------+------+
| city | day1 | day2 | day3 |
+----------+------+------+------+
| Delhi | 2 | 6 | 9 |
| Mumbai | 1 | 3 | 8 |
| Banglore | 4 | 1 | 6 |
+----------+------+------+------+
3 rows in set (0.00 sec)

If current date = '2019-09-01' then I want to select as

SELECT CITY, DAY1 FROM MRTG;

If current date = '2019-09-02' then I want to select as

SELECT CITY, DAY1, DAY2 FROM MRTG;

If current date = '2019-09-02' then I want to select as

SELECT CITY, DAY1, DAY2, DAY3 FROM MRTG;

and so on.

How can I write a query for this?

The values represent day-wise data for a whole month. I do wonder about this type of table structure, but it's the client requirement. I need to find a solution to fetch only the columns from day 1 to the current date.

Answer 1

If you were able to start with a new table I would use this structure to store the data:

CREATE TABLE mrtg (
 city varchar(30)
 , logdate date
 , value int
)

This way you can store data for any time period and work out the days needed from the date.

Since you can't create that we'll have to see if we can modify what you've got in a query. I've had a play and this will get the data into a table format that I believe would be easier to work with:

select data.*
 from mrtg
 left join (
 select city, 1 as day, day1 as datavalues from mrtg
 union all
 select city, 2 as day, day2 from mrtg
 union all
 select city, 3 as day, day3 from mrtg
 #.... 
 #union all
 #select city, 4 as day, day4 from mrtg
 ) data
 on mrtg.city = data.city
 where data.day <= DAYOFMONTH(STR_TO_DATE('2019-08-02','%Y-%m-%d'))

You'll have to add as many union all elements as you have columns but then you can filter on the number of days you want. You will now be able to GROUP BY and perform any calculations you need to.

select data.city, sum(data.datavalues) as TotalToDate
 from mrtg
 left join (
 select city, 1 as day, day1 as datavalues from mrtg
 union all
 select city, 2 as day, day2 from mrtg
 union all
 select city, 3 as day, day3 from mrtg
 #.... 
 #union all
 #select city, 4 as day, day4 from mrtg
 ) data
 on mrtg.city = data.city
 where data.day <= DAYOFMONTH(STR_TO_DATE('2019-08-02','%Y-%m-%d'))
 GROUP BY city

For keeping your main queries cleaner and smaller I'd recommend creating a VIEW for the subquery with all of the UNION ALL statements in.

• AAMIR KHAN: Thanks for detailed explanation. My requirement is fulfilled to some extent but not completely yet.
• AAMIR KHAN: Is there any query or proc which select ONLY non-zero/not null COLUMNS from table. If all the rows of any of the column contains NULL/0 then ignore that column.
• KevH: What is your requirement? Do you need the data to be displayed in the tabular form used in your example?
• AAMIR KHAN: yes. Suppose I've 10 columns out off this 3 columns are totally '0' value. I want to fetch ONLY those 7 columns which are not zero values.
• KevH: Using the structure in my answer you could filter out any non zero columns. How is the query being displayed to the end user? Could the display be handled within the interface they are using (e.g. let the website handle the layout?) or are they using the query directly in within MySQL?

Answer 2

You can try using MySQL JSON type to store the day and value data, as shown here:

CREATE TABLE days (
 id INT PRIMARY KEY,
 city VARCHAR(10),
 data JSON
)

The data column is of type JSON array. Each array element has the day field values 1 to 31, and the val field value as a number. For example, for the first day of a month the data is { "day": 1, "val": 7 }.

Insert some sample data into the table.

INSERT INTO days VALUES (1, 'Rome', '[ { "day": 10, "val": 7 }, { "day": 11, "val": 22 }, { "day": 12, "val": 3 } ]');

I need to find a solution to fetch only the columns from day 1 to the current date.

The query, to get the values until the 11th day:

SET @today = DAYOFMONTH('2023-12-11'); -- for todays day use DAYOFMONTH(CURDATE());
SELECT id, city, JSON_ARRAYAGG(JSON_OBJECT("day", jtab.day, "val", jtab.val)) AS result_data 
FROM 
 days, 
 JSON_TABLE(data, '$[*]' COLUMNS(day INT path '$.day', val INT path '$.val')) AS jtab 
WHERE 
 jtab.day <= @today AND
 jtab.val > 0 AND
 id = 1
GROUP BY id;

The output:

+----+------+---------------------------------------------------+
| id | city | result_data |
+----+------+---------------------------------------------------+
| 1 | Rome | [{"day": 10, "val": 7}, {"day": 11, "val": 22}] |
+----+------+---------------------------------------------------+