CODEWITHSUNDEEP
sqlpostgresqltime-series
Doron Segal
Doron Segal

Reputation: 2260

Postgresql time series interval

I'm using postgres (RDS) for storing time series data.

Let's say my data look like this:

timestamp           | source   |  data
---------------------+----------+------------------
 2017-01-24 19:24:41 |  1       | { some jsonb }
 2017-01-24 19:25:41 |  1       | { some jsonb }
 2017-01-24 19:25:41 |  2       | { some jsonb }
 2017-01-24 19:26:41 |  3       | { some jsonb }
 2017-01-24 19:32:41 |  1       | { some jsonb }
 2017-01-24 19:33:41 |  2       | { some jsonb }
 2017-01-24 19:45:41 |  3       | { some jsonb }
 2017-01-24 19:50:41 |  1       | { some jsonb }
 2017-01-24 19:56:41 |  1       | { some jsonb }
 2017-01-24 20:01:41 |  1       | { some jsonb }

I would like to sort the data by source and to have the data split by interval meaning let's say split by a 15 minutes interval. I also would like round the time when splitting it to interval.

So far I got 

SELECT date_trunc('hour', timestamp) + date_part('minute', timestamp)::int / 15 * interval '15 min' AS fifteen_minutes, data
FROM MY_TABLE
where source=1
GROUP BY data, fifteen_minutes
ORDER BY fifteen_minutes desc

Which returns

fifteen_minutes      | source   |  data
---------------------+----------+------------------
 2017-01-24 19:15:00 |  1       | { some jsonb }
 2017-01-24 19:15:00 |  1       | { some jsonb }
 2017-01-24 19:30:00 |  1       | { some jsonb }
 2017-01-24 19:45:00 |  1       | { some jsonb }
 2017-01-24 19:45:00 |  1       | { some jsonb }
 2017-01-24 20:00:00 |  1       | { some jsonb }

The issue is that I'm still getting multiple results for each interval. I would like to distinct by the interval and get the closest timestamp

Ideally I would like to get: (single result per interval)

fifteen_minutes      | source   |  data
---------------------+----------+------------------
 2017-01-24 19:15:00 |  1       | { some jsonb }
 2017-01-24 19:30:00 |  1       | { some jsonb }
 2017-01-24 19:45:00 |  1       | { some jsonb }
 2017-01-24 20:00:00 |  1       | { some jsonb }

Any better idea? Thanks!

Upvotes: 0

Views: 562

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

select distinct on (fifteen_minutes, source)
    fifteen_minutes, source, data
from (
    select 
        to_timestamp((extract(epoch from timestamp) / (15 * 60))::int * 15 * 60) as fifteen_minutes,
        data, timestamp
    from t
) t
order by
    fifteen_minutes, source,
    abs(extract(epoch from timestamp) - extract(epoch from fifteen_minutes))

Upvotes: 1

Related Questions