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]
CodePudding user response:
Use an outer join against a list of dates:
select g.dt::date, coalesce(m.value, 0) as value
from generate_series(date '2021-12-16', date '2021-12-23', interval '1 day') as g(dt)
left join mytable m
on m.name = 'Jonh'
and m.date::date = g.dt::date
CodePudding user response:
You can try something like
SELECT IF(value is not null, value, 0)
FROM mytable
WHERE name='Jonh'
and (date BETWEEN '2021-12-16 00:00:00' AND '2021-12-23 00:00:00');