Re: Group By Time Stamp
От | Morten Sickel |
---|---|
Тема | Re: Group By Time Stamp |
Дата | |
Msg-id | 54DE9A561AD20C4D9FF88B116965420E029F98@postix.nrpa.no обсуждение исходный текст |
Ответ на | Group By Time Stamp (Ariunbold Gerelt-Od <gereltod@kikakuya.com>) |
Список | pgsql-admin |
> 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
В списке pgsql-admin по дате отправления: