In SQL, how to split timestamps into several intervals but with the first record of the day as the beginning of the first interval?

user_11207179 0
Viewed 120

PostgreSQL 9.5

My dataset looks like this: Sample data

I want to split the records of the same day into a 1-minute interval, but with the beginning of each interval as the min value of the interval.

With this dataset, my ideal output would look like this: Output

For example, in 2014-05-27, the min value is 15:09:40, so the first one-minute interval would be 15:09:40 - 15:10:40, and there're three records. For the next interval, 15:11:01 - 15:12:01, there's one record.

Hope I have made myself clear. Thanks in advance.

timestamp postgresql sql
1 Answers

You can try to use CTE or subquery to get a gap_seconds which represents a value from subtracting of occurred_at and previous occurred_at of row, then making an grp column to calculate values that hold the gap.

;WITH CTE AS (
    SELECT *,
           FLOOR(SUM(gap_seconds) OVER(ORDER BY occurred_at) / 60) grp
    FROM (
        SELECT *,
               EXTRACT(SECOND FROM  occurred_at - LAG(occurred_at,1,occurred_at) OVER(ORDER BY occurred_at)) gap_seconds
        FROM T
    ) t1
)
SELECT MIN(occurred_at) occurred_at,
       COUNT(*)
FROM CTE
GROUP BY grp
ORDER BY 1

sqlfiddle

another example, we can division by 120 second like this below query

WITH CTE AS (
    SELECT *,
           FLOOR(SUM(gap_seconds) OVER(ORDER BY occurred_at) / 120) grp
    FROM (
        SELECT *,
               EXTRACT(SECOND FROM  occurred_at - LAG(occurred_at,1,occurred_at) OVER(ORDER BY occurred_at)) gap_seconds
        FROM T
    ) t1
)
SELECT MIN(occurred_at) occurred_at,
       COUNT(*)
FROM CTE
GROUP BY grp
ORDER BY 1

sqlfiddle

user_51760710
Related