Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I need some help on this:

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.

Can Anyone help me?

share|improve this question
add comment

2 Answers

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

share|improve this answer
add comment

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 :(

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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