I'm using the below MySQL found on the web to generate a list of dates. However, I want it to generate a list of date_times by minute, E.G yyyy-mm-dd hh:mm:ss. Is this possible? I can't wrap my head around how the existing SQL generates the list nevermind adding minutes!
I need this for my base query for a chart / graph, using left join to pull in data from elsewhere.
Thanks
select * from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2019-01-01' and '2019-01-15'
Current output:
selected_date
---
2019-01-01
2019-01-02
2019-01-03
2019-01-04
Desired output:
selected_date
---
2019-01-19 00:05:00
2019-01-19 00:06:00
2019-01-19 00:07:00
2019-01-19 00:08:00
2019-01-19 00:09:00
2019-01-19 00:10:00
2019-01-19 00:11:00
Edit: This is my SQL query serving the graph. I get 5-6 temperature records per minute, so I'm averaging them. My problem is sometimes a temperature probe drops off, so a.date_time or b.date_time doesn't have a consecutive date_time for every single minute
select
b.date_time,
a.temperature as fishtank,
b.temperature as room
from (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
date_time between '$date_from' and '$date_to' and
serial like '28-000898430d59'
group by
date_time div 100
) a
right join (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
date_time between '$date_from' and '$date_to' and
serial like '28-000f9843201e'
group by
date_time div 100
) b on a.date_time = b.date_time
Edit again: I've not managed to generate what I wanted, however I've come up with this which uses existing values in the DB to list dates. I think this will work for me
SELECT
a.date_time,
b.temperature as FishTank,
c.temperature as Room
FROM (
select
convert((min(date_time) div 100)*100, datetime) as date_time
from
raspicontroller.temperature
group by
date_time div 100
) as a
LEFT JOIN (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
serial like '28-000898430d59'
group by
date_time div 100
) as b on a.date_time = b.date_time
LEFT JOIN (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
serial like '28-000f9843201e'
group by
date_time div 100
) as c on a.date_time = c.date_time
