How to get interval by month from table?

Question

I've a table with DATE field (and type too). How can I write a query to get month intervals like this:

   start        end
2015-01-01  2015-01-31
2015-02-01  2015-02-28
2015-03-01  2015-03-31
      and so on ..

What kind a function of SQL I'll use to got this result? I'm use a Postgres DB.

That a query create a table where I store my data:

CREATE TABLE "DEPOSIT_EVENT" (
  id bigint NOT NULL,
  amount numeric(19,2),
  date timestamp without time zone,
  description character varying(255),
  eventtype character varying(255),
  processingdate timestamp without time zone,
  status boolean,
  view boolean,
  deposit_id bigint,
  CONSTRAINT "DEPOSIT_EVENT_pkey" PRIMARY KEY (id ),
  CONSTRAINT fk_dp0pc33hchopclsau0uknefgr FOREIGN KEY (deposit_id)
      REFERENCES "DEPOSITS" (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

I wanna take interval by month only, as I described above.

For example I have several rows:

id    amount  date 
1     100     2014-01-01 08:39:51.774
2     31      2014-01-10 08:39:51.774
3     2       2014-01-21 08:39:51.774
4     22      2014-02-04 08:39:51.774
5     74      2014-03-14 08:39:51.774

and from it I want only month period containing one or more items like this:

         column
2014-01-01 - 2014-01-30
2014-02-01 - 2014-02-28
2014-03-01 - 2014-03-31

you have not provided enough and sufficient info for us to give exact answer to you, maybe this question solve your problem: [Getting results between two dates in PostgreSQL](http://stackoverflow.com/questions/10170544/getting-results-between-two-dates-in-postgresql) — null, Apr 05 '15 at 19:25

score 0 · Accepted Answer · answered Apr 06 '15 at 16:57

The following should work:

SELECT 
    date_trunc('MONTH', date) as start_date, 
    date_trunc('MONTH', date) + INTERVAL '1 MONTH - 1 day' as end_Date, 
    COUNT(id) AS recordcount 
FROM DEPOSIT_EVENT 
GROUP BY 1,2

This gets the start of the month and the end of the month for each date, then does a GROUP BY on both of these. I added in the third field recordcount just to show that you could also get a count of records within that time period in the same query.

How to get interval by month from table?

1 Answers1