grouping by date increments

From: "Graham Vickrage" <graham(at)digitalplanit(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: grouping by date increments
Date: 2001-01-23 11:41:40
Message-ID: NDBBJABDILOPAOOMFJHOMECGCHAA.graham@digitalplanit.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I am trying to write a select statement to count the occurences of a
particular string between a set of dates.

I have written this successfully but need to get the count in time
increments such as per day/week/month.

At the moment I am doing a select for each increment seperately but figure
that as its doing a seqential scan then it may be possible to do it all at
once.

Has anyone done anything similar that or maybe could recommend a more
efficient solution.

Thanks

Graham

current select:

SELECT to_char(timestamp('01-Jun-2000'), 'DD-Mon-YYYY'),
to_char(timestamp('01-Aug-2000'), 'DD-Mon-YYYY'), count(*) FROM table WHERE
date >= timestamp('01-Jun-2000') AND date < timestamp('01-Aug-2000') AND
text = 'FOOBAR';

Attachment Content-Type Size
winmail.dat application/ms-tnef 1.8 KB

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Attila Kevei 2001-01-23 11:42:27 select returns no line
Previous Message Ramesh H R 2001-01-23 11:36:10 postgresql.largeobject package