Re: Group By Time Stamp

> From: Ariunbold Gerelt-Od [mailto:gereltod(at)kikakuya(dot)com]
> Hi,
>
> I have a table Answers with fields answer_id
> integer,time_stamp timestamp.
>
> How can I create a query that gives a sum of answer_id=ID
> ,but in every
> 2 hours.
>
What you probably need is the function
extract(hour from time_stamp)
which will give you the hour.
Then, to get a bi-hourly gruping, what I would do is to divide by 2 use
round to get rid of the fraction and then multiply by 2, ie:

select distinct round(extract(hour from time_stamp)/2)*2 as bihour

If you need also to keep track of the date, that is probably best done as an
separate extra field created by date(time_stamp)

So I think that

select count(answer_id),date(time_stamp) as date, distinct
round(extract(hour from time_stamp)/2)*2 as bihour
from Answers
group by date,bihour

should get out the data you need, then you might need to do some more
formatting on it.

(If you have a large table, put in some extra filters in select when you are
testing it....)

Morten

--
Morten Sickel
Norwegian Radiation Protection Authority