How to Query data from a table where Minutes, Days, Months and Years are stored in separate columns

Question

I have a legacy mysql database where its Date information is stored in separate columns alongside with the other data columns as as in the attached screenshot.

I want to filter data for a specific range using date time values or preferably timestamps.

What would be the best approach for this?

Please find the insert queries for the sample data

create table MOCK_DATA (
    ServiceName VARCHAR(5),
    StatusType VARCHAR(8),
    Status VARCHAR(7),
    Year VARCHAR(4),
    Month VARCHAR(10),
    Day VARCHAR(10),
    Minute VARCHAR(10),
    Count VARCHAR(10)
);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test2', 'statusT1', 'status1', 2020, 7, 11, 29, 183);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test4', 'statusT4', 'status4', 2019, 4, 16, 15, 120);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test4', 'statusT1', 'status1', 2019, 8, 10, 37, 66);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test3', 'statusT4', 'status1', 2022, 6, 22, 32, 78);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test2', 'statusT2', 'status1', 2019, 9, 29, 36, 132);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test1', 'statusT4', 'status3', 2022, 11, 10, 29, 185);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test2', 'statusT4', 'status2', 2019, 8, 10, 56, 175);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test3', 'statusT1', 'status2', 2020, 6, 9, 24, 124);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test4', 'statusT4', 'status1', 2021, 3, 2, 40, 185);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test3', 'statusT4', 'status1', 2019, 5, 2, 12, 198);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test3', 'statusT1', 'status3', 2020, 12, 30, 33, 186);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test4', 'statusT4', 'status1', 2022, 10, 11, 26, 142);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test1', 'statusT3', 'status2', 2022, 10, 9, 18, 42);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test2', 'statusT1', 'status2', 2020, 1, 29, 33, 56);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test1', 'statusT3', 'status2', 2022, 4, 6, 33, 193);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test1', 'statusT1', 'status1', 2020, 2, 13, 20, 43);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test1', 'statusT3', 'status3', 2022, 10, 30, 36, 47);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test4', 'statusT4', 'status4', 2021, 2, 28, 56, 97);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test4', 'statusT3', 'status2', 2021, 2, 28, 8, 194);
insert into MOCK_DATA (ServiceName, StatusType, Status, Year, Month, Day, Minute, Count) values ('test2', 'statusT3', 'status2', 2021, 5, 27, 36, 199);

Answer 1

MySQL 5.7 and later support virtual columns and indexes on virtual columns:

ALTER TABLE MyTable 
  ADD COLUMN ServiceDate DATE AS (STR_TO_DATE(CONCAT(`Year`, '-', `Month`, '-', `Day`), '%Y-%m-%d')), 
  ADD KEY (ServiceDate);

You can use EXPLAIN to verify that it uses that index:

EXPLAIN SELECT * FROM mytable WHERE ServiceDate = CURDATE();
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | mytable | NULL       | ref  | ServiceDate   | ServiceDate | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

If you want timestamps, you can make the virtual column a DATETIME and make the virtual column expression based also use the hour, minute, and second columns.