Обсуждение: Group By Time Stamp

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.

For example:

answer_sum   time_stamp

10           2002-03-24 20-22
12           2002-03-24 22-24
20           2002-03-25 00-02
4            2002-03-24 04-06

..............

and so on.

> From: Ariunbold Gerelt-Od [mailto:gereltod@kikakuya.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