Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Question Unanswered: Groupin entries by the timestamp range.

    I have the following problem and will appreciate any help with it.
    In the events table I am storing some event information which include event source and event timestamp:

    Code:
    CREATE TABLE events (
    source_id INTEGER,
    event_type SMALLINT,
    event_occured TIMESTAMPTZ
    ) WITH OID;
    I need to retrieve this information for a particular period.
    My problem is that events can have ripples, i.e. same event can generate several entries with slightly different timestamp. I need to eliminate these ripples. When two or more entries from the same source and event type exist with the timestamp within 30 sec from each other, they must be considered as one entry.
    I tried to use GROUP BY or self join, but still cannot get the right answer.
    Any ideas?

    BTW, I am using PostgreSQL 8.2.4

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    could you show us some sample data, both with and without 'ripples' ?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jun 2009
    Posts
    5
    Quote Originally Posted by loquin
    could you show us some sample data, both with and without 'ripples' ?
    5001 1 2009-05-31 10:05:01.0 <- event #1
    5001 1 2009-05-31 10:05:03.0 <- event #1 ripple
    5002 2 2009-05-31 10:05:02.0 <- event #2
    5001 1 2009-05-31 10:05:07.0 <- event #1 ripple
    5001 1 2009-05-31 10:05:12.0 <- event #1 ripple
    5002 2 2009-05-31 10:05:18.0 <- event #2 ripple
    5001 1 2009-05-31 10:05:49.0 <- event #3
    5001 1 2009-05-31 10:06:03.0 <- event #3 ripple
    5002 1 2009-05-31 10:05:14.0 <- event #4

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •