SQL Query Single Value from Table in a Date Range

December 23, 2021

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]

>Solution :

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