3

i use psql v.10.5

and i have a structure table like this :

|     date      | total |
-------------------------
|  01-01-2018   |   50  |
|  05-01-2018   |   90  |
|  30-01-2018   |   20  |

how to get recap data by month, but the data showed straight 30 days, i want the data showed like this :

|     date      | total |
-------------------------
|  01-01-2018   |   50  |
|  02-01-2018   |   0   |
|  03-01-2018   |   0   |
|  04-01-2018   |   0   |
|  05-01-2018   |   90  |
.....
|  29-01-2018   |   0   |
|  30-01-2018   |   20  |

i've tried this query :

SELECT * FROM date
WHERE EXTRACT(month FROM "date") = 1  // dynamically
AND EXTRACT(year FROM "date") = 2018  // dynamically

but the result is not what i expected. also the params of month and date i create dynamically.

any help will be appreciated

DarkCode999
  • 182
  • 1
  • 1
  • 11
  • use `generate_series` example: https://stackoverflow.com/questions/14113469/generating-time-series-between-two-dates-in-postgresql – Paul Maxwell Oct 04 '18 at 02:29

1 Answers1

5

Use the function generate_series(start, stop, step interval), e.g.:

select d::date, coalesce(total, 0) as total
from generate_series('2018-01-01', '2018-01-31', '1 day'::interval) d
left join my_table t on d::date = t.date

Working example in rextester.

klin
  • 112,967
  • 15
  • 204
  • 232
  • 3
    To add to the above answer +1, this is an example of using a _calendar_ table, which contains all the dates you want to appear in your report, even if all dates don't appear in your actual data. – Tim Biegeleisen Oct 04 '18 at 02:30