1

Is there a way to build a query that returns a record set consisting of a date and the number of records == that date? For example, I have a DB that is tracking inspections that take place on every day. I want to build a data set to be used for a trending graph to see how many inspections took place on each day streching back 365 days.

Record set desired would look like this:

date | InspectionCount
2013-12-30  265
2013-12-29  123
2013-12-28  425
.
.
.
2013-01-01  15

Is there a way to do that without write code that will simple loop through 365 times and sort of manually generate daily counts like this:

select count(reporting_id) as InspectionCount
from inspection
where close_conf_date >= '2013-12-30'
and close_conf_date < '2013-12-31'

while gradually winding the clock back 365 days? Please let me know if you need more info.

Update

I have just tried this:

WITH RECURSIVE t(n) AS (
VALUES (cast('2013-12-30' as timestamp))
UNION ALL
SELECT n-interval '1 day' FROM t WHERE n >=('2013-12-30'-interval '365 day')
)
SELECT  cast(t.n as date) as date_column, count(reporting_id) 
FROM t
INNER JOIN inspection i ON (cast(t.n as date) = cast(i.close_conf_date as date))
GROUP BY cast(t.n as date)

and I am getting this error i do not really understand: interval field value out of range: "2013-12-30"

  • If you don't care about days with 0 inspections, just use a regular sum/group by query. Otherwise you'll need interval and windowed functions – Sebas Jul 9 '14 at 18:27
3

You don't need the recursive CTE, just use generate_series():

You also need to use an outer join in order for the days without inspection to show up as zero:

with days as (
  select day::date
  from generate_series(date '2013-12-30', date '2013-12-30' + 365, interval '1' day) day
) 
select d.day as date_column, 
       count(i.reporting_id) as cnt
from days d
  left join inspection i on i.date = d.day
group by d.day

generate_series(date '2013-12-30', date '2013-12-30' + 365, interval '1' day) generates one row for each date between '2013-12-30' and '2013-12-30' + 365. For details on generate_series() see the manual: http://www.postgresql.org/docs/current/static/functions-srf.html

The ::date notation is just a shorthand for cast(.. as date)

1

You can use recursive query to generate 365 rows and join your original query with it . For instance (recursive part) :

WITH RECURSIVE t(n) AS (
    VALUES (cast(current_date as timestamp))
  UNION ALL
    SELECT n-interval '1 day' FROM t WHERE n >=(current_date-interval '365 day')
)
SELECT  cast(n as date) as date_column FROM t;

All together (one year running total - from today minus 365 days till today)

WITH RECURSIVE t(n) AS (
        VALUES (cast(current_date as timestamp))
      UNION ALL
        SELECT n-interval '1 day' FROM t WHERE n >=(current_date-interval '365 day')
    )
    SELECT  cast(t.n as date) as date_column, count(reporting_id) 
    FROM t
    INNER JOIN inspection i ON (cast(t.n as date) = cast(i.close_conf_date as date))
    GROUP BY cast(t.n as date)

You may use " n > ..." instead of ">= ..." if it returns more days then needed. Also, casting i.close_conf_date to date might be redundant

  • What does n stand for, 2013-12-31? How would directly translate my query above into what you have there? Thanks. – Mr. Concolato Jul 9 '14 at 19:50
  • check updated version. In case I misunderstood the question (I thought you need a running total), just change current_date to whatever end date you want. – a1ex07 Jul 9 '14 at 20:27
  • I have updated what i have tried so far in the main question, and the error that i get. Do i have the dates backwards? – Mr. Concolato Jul 9 '14 at 21:47
  • @Mr.Concolato : do date '2013-12-30' - interval .... : ` .... SELECT n-interval '1 day' FROM t WHERE n >=(date '2013-12-30'-interval '365 day') ....` – a1ex07 Jul 9 '14 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.