I need to make a query to a table that contains this columns:
name | value | date
--------+-------+--------+---------
Jonh | 0.15 | 2021-12-23 00:00:00
Jonh | 0.14 | 2021-12-22 00:00:00
Jonh | 0.19 | 2021-12-21 00:00:00
Jonh | 0.13 | 2021-12-15 00:00:00
Bob | 0.12 | 2021-12-15 00:00:00
I need to select, for a given name, all the values for the last 7 days. I could just do:
SELECT value FROM mytable WHERE name='Jonh' AND (date BETWEEN '2021-12-16 00:00:00' AND '2021-12-23 00:00:00')
But this will only fetch the values in DB, I need to get a list of 7 items, with a default value of 0 if there is no value stored in DB for that day, I’m about to create a loop in backend that iterates over each date and returns the value on DB or a default value 0, but I was wondering if I can do this with SQL, any suggestions?
The desired output would be, for the name ‘Jonh’ and making the query today:
[0, 0, 0, 0, 0.19, 0.14, 0.15]