Group By Time Stamp
Group By Time Stamp
От:
Ariunbold Gerelt-Od <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. 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.
Re: Group By Time Stamp
От:
Morten Sickel <Morten.Sickel@nrpa.no>
Дата:
> 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