10

I need to group by a period of time spliting up by 10 minutes.

I have this query below that returns me a result set:

SELECT timestamp
    FROM table_1, table_2_shape_polygon
    WHERE ST_Within(earthnetworks_geometry_raios, poligono_geometry)
      AND timestamp 
      BETWEEN '2013-04-07 15:30:00' AND '2013-04-07 16:50:00'
      AND type = 0
      AND id_polygon = 2

My result set is like this:

"2013-04-07 15:30:55"
"2013-04-07 15:32:52"
"2013-04-07 15:32:52"
"2013-04-07 15:34:21"
"2013-04-07 15:39:09"
"2013-04-07 16:24:25"
"2013-04-07 16:29:58"
"2013-04-07 16:33:22"
"2013-04-07 16:34:30"
"2013-04-07 16:35:09"
"2013-04-07 16:36:54"
"2013-04-07 16:38:40"
"2013-04-07 16:39:37"
"2013-04-07 16:39:37"
"2013-04-07 16:39:38"
"2013-04-07 16:39:38"
"2013-04-07 16:39:44"
"2013-04-07 16:39:56"
"2013-04-07 16:40:03"
"2013-04-07 16:40:04"
"2013-04-07 16:41:22"
"2013-04-07 16:41:27"
"2013-04-07 16:41:38"
"2013-04-07 16:41:38"
"2013-04-07 16:42:24"
"2013-04-07 16:42:39"
"2013-04-07 16:45:00"
"2013-04-07 16:45:40"
"2013-04-07 16:49:43"

I have all timestamps in between a period, but I need to group it every 10 minutes and I have no idea how. I tried date_trunc which does not have the precision I need.

ex: between 2013-04-07 15:30:00, 2013-04-07 15:40:00 5 results.

How can I do this?


I tried this and it did not work as expected.

SELECT
    timestamp 'epoch' +
    INTERVAL '1 second' * round((extract('epoch' from earthnetworks_dt_horario) / 600) * 600) as horario,
    count(earthnetworks_dt_horario)
FROM raios.earthnetworks, raios.earthnetworks_teste_poligono
WHERE ST_Within(earthnetworks_geometry_raios, poligono_geometry)
      AND earthnetworks_dt_horario 
      BETWEEN '2013-04-07 15:30:00' AND '2013-04-07 16:50:00'
      AND earthnetworks_num_tipo = 0
      AND id_poligono = 2
GROUP BY 
round(extract('epoch' from earthnetworks_dt_horario) / 600), earthnetworks_dt_horario

This is the result set not grouped:

"2013-04-07 16:29:58";1
"2013-04-07 16:34:30";1
"2013-04-07 16:33:22";1
"2013-04-07 16:39:44";1
"2013-04-07 16:39:56";1
"2013-04-07 16:42:24";1
"2013-04-07 16:41:38";2
"2013-04-07 16:24:25";1
"2013-04-07 16:39:38";2
"2013-04-07 16:42:39";1
"2013-04-07 16:41:27";1
"2013-04-07 16:36:54";1
"2013-04-07 16:45:00";1
"2013-04-07 16:40:04";1
"2013-04-07 16:40:03";1
"2013-04-07 15:34:21";1
"2013-04-07 15:30:55";1
"2013-04-07 15:39:09";1
"2013-04-07 16:49:43";1
"2013-04-07 16:45:40";1
"2013-04-07 16:35:09";1
"2013-04-07 16:38:40";1
"2013-04-07 16:39:37";2
"2013-04-07 16:41:22";1
"2013-04-07 15:32:52";2

It did not work.

closed as off-topic by Mike T, BERA, Kadir Şahbaz, whyzar, Evil Genius Oct 15 '18 at 18:14

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general IT or with no clear GIS component, are off-topic here but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software), Database Administrators (relational databases) and other SE sites" – Mike T, BERA, Kadir Şahbaz, whyzar, Evil Genius
If this question can be reworded to fit the rules in the help center, please edit the question.

11

You can use this for PostgreSQL. 600 is 10 minutes in seconds. The idea is to convert timestamp to epoch, divide by interval desired in minutes then round to get the desired interval

SELECT COUNT(*) cnt, 
to_timestamp(floor((extract('epoch' from timestamp_column) / 600 )) * 600) 
AT TIME ZONE 'UTC' as interval_alias
FROM TABLE_NAME GROUP BY interval_alias
2

you can use this answer by replacing 300 by 600 (the number of seconds in 10 minutes)

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

2

There are a few handy datetime functions. First, deconstruct the timestamps with date_trunc to each hour, then do some integer division with the minutes with date_part to make an interval, which is recombined.

SELECT COUNT(*), 
    date_trunc('hour', timestamp) +
    (((date_part('minute', ts)::integer / 10::integer) * 10::integer)
     || ' minutes')::interval AS ten_min_timestamp
FROM sometable_or_joins_or_whatever
GROUP BY ten_min_timestamp;
  • This works well with all the conditions, must have been upvoted. But I am not sure why || ' minutes' has been used in date_part. – Vishal Shetty Oct 25 '18 at 11:44
  • integer || 'minutes' is used to create an interval type, for example "8 minutes", which is added to the timestamp. – Mike T Oct 25 '18 at 20:45
1

I did something similar in mySql by grouping by timestamp - MOD(timestamp, 600), the return value of which will be same for all timestamps within a 10 min (600 sec) intevall.

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