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

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);

CodePudding user response：

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.

CodePudding user response：

You can use CONCAT function to concatenate the columns and then use a subquery to filter the data.

Example:

SELECT date from (SELECT CAST(CONCAT(year, "-", month, "-", day) AS DATE) AS date from MOCK_DATA) as date WHERE date BETWEEN '2020-07-10' AND '2022-07-20'

This will return;

date
2020-07-11
2022-06-22
2021-03-02
2020-12-30
2022-04-06
2021-02-28
2021-02-28
2021-05-27