Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a large postgreSQL database with log data. All this data has timestamps and I want to group consecutive rows where the difference between the timestamps is less then 1500 miliseconds for example.

For example:

1349427083272
1349427083669
1349427083707
1349427084277
1349427084787
1349427093471
1349427094031
1349427094307
1349427094980
1349427095879
1349427097211
1349437622947
1349437623813
1349437624316
1349437624815
1349437624938

Should result in the following groups:

1349427083272
1349427083669
1349427083707
1349427084277
1349427084787

1349427093471
1349427094031
1349427094307
1349427094980
1349427095879
1349427097211

1349437622947
1349437623813
1349437624316
1349437624815
1349437624938

The group identifier can just be a unique integer.

In MySQL I found a similar example which used lots of variables, but I have no clue how to to this in postgreSQL. Could someone help me out?

share|improve this question
    
You can probably use windowing functions to find out where the group boundaries are (sqlfiddle.com/#!12/015e6/13/0). I know you can create group IDs in Oracle with grouping functions, but I don't think Postgres has grouping functions yet... –  FrustratedWithFormsDesigner May 7 '13 at 14:44

2 Answers 2

up vote 2 down vote accepted

With the comment from FrustratedWithFormsDesigner, I came to the following solution:

SELECT subq2.*, sum(new_group) OVER (ORDER BY t ASC) AS group_id
FROM (
  SELECT subq.*, CASE WHEN delta > 1500 THEN 1 ELSE 0 END AS new_group
  FROM (
    SELECT t, lag(t) over (ORDER BY t ASC),
      t - lag(t) over (ORDER BY t ASC) AS delta
    FROM time_points
  ) AS subq
) AS subq2

I am using the sum of all new_group values to create different groups. Thank you very much!

share|improve this answer

If it is sufficient that the group boundaries be arbitrary time periods (like 1000000-10001499, 10001500-10002999, ...) then GROUP BY int_timestamp/1500 should do, and you can take MIN(int_timestamp) as the ID for the group.

Or are you actually trying to output the whole rows grouped by 1500ms instead of just aggregates of the groupings? It would be helpful to include fuller sample input and output data in your question.

share|improve this answer
    
I want to "categorize" the whole rows. The log values were written with 2Hz during events so the time difference should be 500ms between all logs of one event. Sometimes logs are not written so the difference is greater than 500ms so i chose 1500ms because it is still smaller than the expected time between events. What I want to have is an "event id" in each row, so that I can examine the log rows of specific events. There's not much input, just all the data rows in the table including the timestamp, but there#s nothing useful for grouping. –  six86 May 7 '13 at 13:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.