Skip site navigation (1) Skip section navigation (2)

grouping by time interval

From: Michael Lush <mjlush(at)ebi(dot)ac(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: grouping by time interval
Date: 2009-04-01 15:08:35
Message-ID: Pine.LNX.4.64.0904011532580.2612@pigeon.ebi.ac.uk (view raw or flat)
Thread:
Lists: 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(at)genenames(dot)org
European Bioinformatics Institute
Hinxton, Cambridge
URL: http://www.genenames.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In response to

Responses

pgsql-novice by date

Next:From: Larry RosenmanDate: 2009-04-01 20:05:27
Subject: Re: grouping by time interval
Previous:From: Bruce MomjianDate: 2009-04-01 14:56:01
Subject: Re: [personal] Re: How to out \df to a file

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group