使用JSON格式的SQL提取数据，按时间戳分组

Question

我有一个遗留的MariaDB数据库，它以以下方式保存我们的温度和湿度传感器的历史数据

值温度哼声温度hum

id	日期	标记
1	2021-11-10 08:08:13		21.3
2	2021-11-10 08:08:13	52.7
3	2021-11-10 09:08:13	23.3
4	2021-11-10 09:08:13	57.7

查询：

select 
JSON_ARRAYAGG(
JSON_OBJECT(
'ts', t1.date, 'ts', t2.date, 'temp', 
tValue, 'hum', hValue
)
) 
from 
(
SELECT 
date, 
value as "tValue" 
from 
HistoricalData 
where 
tag = "temp"
) t1 
join (
SELECT 
date, 
value as "hValue" 
from 
HistoricalData 
where 
tag = "hum"
) t2 on t1.date = t2.date;

结果：

[
{
"ts":"2021-11-10 08:08:13.000000",
"hum":52.70000076293945,
"temp":21.299999237060547
},
{
"ts":"2021-11-10 09:08:13.000000",
"hum":57.70000076293945,
"temp":23.299999237060547
}
]