5

I would love to know how to create a view that groups timestamps in 10 minute nearest 10 minute intervals and contains each minimum and maximum timestamp for each.

So a table that looks like this:

| Hero         | timestamp           |

| Batman       | 2016-12-08 12:00:00 |
| Batman       | 2016-12-08 12:07:00 |
| Batman       | 2016-12-08 13:00:00 |
| Batman       | 2016-12-08 14:00:00 |
| Wonder Woman | 2016-12-08 10:15:00 |
| Wonder Woman | 2016-12-08 10:18:00 |
| Wonder Woman | 2016-12-08 10:25:00 |
| Wonder Woman | 2016-12-08 10:30:00 |

would result in a view like this

| Hero         | start_time          | end_time            |

| Batman       | 2016-12-08 12:00:00 | 2016-12-08 12:07:00 |
| Wonder Woman | 2016-12-08 10:15:00 | 2016-12-08 10:30:00 |

or this:

| Hero         | start_time          | end_time            |

| Batman       | 2016-12-08 13:00:00 | NULL                |
| Batman       | 2016-12-08 14:00:00 | NULL                |
| Batman       | 2016-12-08 12:00:00 | 2016-12-08 12:07:00 |
| Wonder Woman | 2016-12-08 10:15:00 | 2016-12-08 10:30:00|

Either solution would be fine.

  • 1
    The last result for 'Wonder Woman' in each result has more than an hour between rows and contradicts the rest of your question. Please clarify the criteria to group rows. – Erwin Brandstetter Dec 8 '16 at 23:26
  • Sorry, you are totally right. I fixed the last line. – Wurstsalat Dec 9 '16 at 10:06
  • So, if I understand well, what you wish is to find the start and end times of a sequence of events, with a gap between events no larger than 10 minutes. Is it right? That is: Wonder Woman gets just one entry because in the sequence: 10:15, 10:18, 10:25 and 10:30 there is never a gap larger than 10 minutes, even if those events are not all in the same "ten minutes bin". – joanolo Dec 9 '16 at 12:31
  • That is correct. The second solution from @ErwinBrandstetter solves exactly this. – Wurstsalat Dec 9 '16 at 17:32
3

Raster of 10-minute intervals

I suggest to group by a combination of "hour" and 10-minute interval:

SELECT hero
     , min(timestamp) AS start_time
     , CASE WHEN count(*) > 1 THEN max(timestamp) END AS end_time
FROM   tbl
GROUP  BY hero
     , date_trunc('hour', timestamp)
     , EXTRACT(MINUTE FROM timestamp)::int / 10
ORDER  BY 1, 2;  -- optional

Working with date / time functions.

EXTRACT(minute FROM timestamp) extracts the minute part of the time The expression. After the cast to integer (::int), integer division (/ 10) effectively rounds to 10-minute intervals (0 - 5).

The CASE expression only adds an end_time if more than one rows fall in the same 10-minute interval.

I advise not to use the "timestamp" as identifier. It's a reserved word in standard SQL and base data type in Postgres.

Groups defined by gaps of 10 or more minutes

If "groups" are defined by gaps of 10 minutes or more between rows of the same hero:

SELECT hero
     , count(*) AS ct  -- optional
     , min(timestamp) AS start_time
     , CASE WHEN count(*) > 1 THEN max(timestamp) END AS end_time
FROM  (
   SELECT hero, timestamp, count(step OR NULL) OVER (ORDER BY hero, timestamp) AS grp
   FROM  (
      SELECT *
           , lag(timestamp) OVER (PARTITION BY hero ORDER BY timestamp)
           < timestamp - interval '10 min' AS step
      FROM   tbl
      ) sub1
   ) sub2
GROUP  BY hero, grp;

Detailed explanation:

SQL Fiddle for both.

  • Hm, this does not work in any case as expected right? If the minute part isn't in the same decimal (ie. 20:10:00, 20:19:00 and 20:25:00) the query begins a new group. – Wurstsalat Dec 8 '16 at 20:46
  • 2
    Depends on what you expected. I am grouping along 10-minute intervals like you mentioned in your question. If that's not what you want ask a question with an exact definition of how to form groups. – Erwin Brandstetter Dec 8 '16 at 21:28
  • Your solution is perfect. Hoassn Dampf! – Wurstsalat Dec 9 '16 at 10:08
2

If instead of rounding time intervals you would like to round integer numbers, let's say to "the nearest dozen", you would do the follwing operation:

number::integer / 12 * 12 

Although dividing and multiplying by 12 seems to "just do nothing", it actually gets rid of the "fraction of a dozen" in your number. The trick lies in the fact that your division is an integer division, and the result is an integer number with no fractional part.

Try, for instance:

SELECT
     1 / 12 * 12 AS n1,
     2 / 12 * 12 AS n2,
    11 / 12 * 12 AS n11,
    12 / 12 * 12 AS n12,
    13 / 12 * 12 AS n13,
    23 / 12 * 12 AS n23,
    24 / 12 * 12 AS n24,
    25 / 12 * 12 AS n25,
    35 / 12 * 12 AS n35 ;

This is a standard way to "rounding down" numbers to bin them, given a certain bin size (12, in this case).

You can do the same with times, as sooun as you convert timestamps to integers (or bigints). You do that by representing timestamps as "number of seconds since a certain date and time". The "epoch" of a timestamp represents the number of seconds elapsed since a certain time and date (1st Jan 1970, 00:00:00). PostgreSQL (and I think, SQL in general) uses "EXTRACT (EPOCH FROM ts)" to get this value.

If you want to round these epochs to any interval (in your case 10 minutes = 60 * 10 seconds), you can make an integer division of this epoch by 600 (so, you get rid of the decimals) and multipy back by this 600. This way, you get rid of the "fractional part" of your 10 minute intervals.

The following code will give you the second version of your possible solutions (with a couple of extra rows for Wonder Woman):

CREATE TABLE hero_stamps (hero text, ts timestamp without time zone) ;

INSERT INTO 
    hero_stamps (hero, ts)
VALUES
    ('Batman', '2016-12-08 12:00:00'),
    ('Batman', '2016-12-08 12:07:00'),
    ('Batman', '2016-12-08 13:00:00'),
    ('Batman', '2016-12-08 14:00:00'),
    ('Wonder Woman', '2016-12-08 10:15:00'),
    ('Wonder Woman', '2016-12-08 10:18:00'),
    ('Wonder Woman', '2016-12-08 10:25:00'),
    ('Wonder Woman', '2016-12-08 11:30:00') ;

SELECT
    hero, 
    start_time, 
    (CASE WHEN start_time = end_time THEN NULL ELSE end_time END) AS end_time
FROM
(
    SELECT
        hero, 
        extract(epoch from ts)::bigint   /* timestamp converted to epoch */
           / (60 * 10)::integer          /* integer div */
           * (60 * 10)::integer          /* multiply back to have start time */
        AS epoch_start_time_of_interval,
        min(ts) AS start_time, 
        max(ts) AS end_time 
    FROM
        hero_stamps
    GROUP BY
        hero, epoch_start_time_of_interval
) AS s0
ORDER BY
    hero, start_time ;

The GROUP BY makes the "binning", the min(ts) and max(ts) gives you the maximum and minimum values, and the extra function in the outermost SELECT gives the nulls the way you want (and gets rid of the "epoch_start_time_of_interval" that you're not interested in).

This approach will work even if you have timestamps with the same "hour" and "minute" but different calendar days, because it does not ignore this information (it gets embedded in the "epoch").

A more compact version (giving the same results) would be

SELECT
    hero, 
    min(ts) AS start_time, 
    (CASE WHEN count(*) = 1 THEN NULL ELSE max(ts) END) AS end_time 
FROM
    hero_stamps
GROUP BY
    hero, 
    extract(epoch from ts)::bigint / 600
ORDER BY
    1, 2 ;
  • 1
    This seems like a verbose copy of my first query. The only variation is extracting the epoch - one column less to group by, ok. Aside: CASE WHEN start_time = end_time in the long version is subtly different from CASE WHEN count(*) = 1 in the "compact" version. – Erwin Brandstetter Dec 8 '16 at 23:45
  • 1
    Seems like you are misunderstanding date_trunc(). There is no confusion among days. Try it. – Erwin Brandstetter Dec 9 '16 at 4:12
  • You're right!, I was confusing date_trunc() with date_part(). Thanks for the clarification. Your solution and mine give actually the same results. The (not very practical) difference would be that converting to epoch gives you the ability to make your intervals of any number of seconds (let's say 137), which in practice I don't think is used in practice. – joanolo Dec 9 '16 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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