grouping by time interval
От | Michael Lush |
---|---|
Тема | grouping by time interval |
Дата | |
Msg-id | Pine.LNX.4.64.0904011532580.2612@pigeon.ebi.ac.uk обсуждение исходный текст |
Ответ на | How to out \df to a file ("Craig" <craig@mysoftforge.com>) |
Ответы |
Re: grouping by time interval
|
Список | pgsql-novice |
I'm writing a photo database for my family photos. I've got a massive back log so I'd like to some large scale annotation and break things up into manageable chunks. I think one useful assumption would be tha any set of photos taken within of a few seconds of each other (say 5 or less) are /probably/ about the same subject... How would I go about detecting and marking events where the time interval between two photos is less than 5 seconds photo_id | photo_time | photo_group 1 | 2005-08-20 19:05:15 | 2 | 2005-08-20 19:12:59 | 3 | 2005-08-20 19:13:03 | 4 | 2005-08-21 19:13:10 | 6 | 2005-08-21 13:02:48 | 7 | 2005-08-21 13:04:58 | 8 | 2005-08-21 13:06:06 | 9 | 2005-08-21 13:06:10 | 10 | 2005-08-21 13:09:19 | would become something like photo_id | photo_time | photo_group 1 | 2005-08-20 19:05:15 | 2 | 2005-08-20 19:12:59 | 1 3 | 2005-08-20 19:13:03 | 1 4 | 2005-08-21 19:13:07 | 1 6 | 2005-08-21 13:02:48 | 7 | 2005-08-21 13:04:58 | 8 | 2005-08-21 13:06:06 | 2 9 | 2005-08-21 13:06:10 | 2 10 | 2005-08-21 13:06:19 | My first thought was to reach for perl :-) but I was wondering if there was an 'easy' way to do it in postgresSQL ... -- Michael ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Michael John Lush PhD Tel:44-1223 492626 Bioinformatician HUGO Gene Nomenclature Committee Email: hgnc@genenames.org European Bioinformatics Institute Hinxton, Cambridge URL: http://www.genenames.org ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
В списке pgsql-novice по дате отправления: