I don't understand how to use generate_series to populate an HTML table where the 'key' of <tr> is the days of the month depending on the month and year that is selected with an <input type='month'>.
For now (thanks to the answers I got right here), I generated the query that only brings me the days of the month and year when I select it in the select month.
Something like this:
SELECT
*
FROM (
SELECT
folio,
fecha,
extract(month FROM fecha) = $3 AS mes,
EXTRACT(YEAR FROM fecha) = $4 AS anio,
temperatura
FROM
medicion) m
WHERE
mes = TRUE
AND anio = TRUE
ORDER BY
fecha ASC
And got this in the HTML table:
| fecha | temperatura |
|---|---|
| 2022-03-04 | 60 |
| 2022-03-07 | 50 |
| 2022-03-09 | 70 |
But I need something like this:
| fecha | temperatura |
|---|---|
| 2022-03-01 | 0 |
| 2022-03-02 | 0 |
| 2022-03-03 | 0 |
| 2022-03-04 | 60 |
| 2022-03-05 | 0 |
| 2022-03-06 | 0 |
| 2022-03-07 | 50 |
| 2022-03-08 | 0 |
| 2022-03-09 | 70 |
| 2022-03-10 | 0 |
with all days and different for each month.
Something like this:
SELECT
t.dt,
coalesce(temperatura, 0)
FROM
generate_series('03/01/2022'::timestamp, (('03/01/2022'::timestamp + interval '1 month') - interval '1 day'), '1 day') AS t (dt)
LEFT JOIN
medicion ON t.dt = medicion.fecha;
This assumes medicion.fecha is a date field. Also you would need to build the start date for the generate_series() from the input values to enter into the function, for instance $3/01/$4