1

Let's say I have 2 models, Visitor and Visits. Is it possible to group all the Visits for a particular Visitor that are no more than 30 minutes apart (or any time interval really). So it will basically split the Visits up where there is a gap of more than 30 minutes. E.g. If the visits are created at 10:00, 10:05, 10:14, 11:20, 11:30 I would get two groups: 10:00, 10:05, 10:14 and 11:20, 11:30. Thanks!

aquint
  • 512
  • 3
  • 14
  • What do you want to do with these groups once you have them? I.e. what would the desired output, in table format, be? This type of thing is usually better done procedurally than in SQL. – jjanes Jun 29 '15 at 17:39
  • You need something like clustering. I bet this hardy achievable in sql. – mikdiet May 25 '17 at 20:12

2 Answers2

1
select to_timestamp(floor((extract('epoch' from your_timestamp_column) / 1800 )) * 1800) from your table
group by to_timestamp(floor((extract('epoch' from your_timestamp_column) / 1800 )) * 1800)

Will give you your times at 30 minute intervals.

Philip Devine
  • 1,169
  • 5
  • 11
1

related post for postgresql: Rails & Postgresql: how to group queries by hour?. there should be one for mysql as well, i believe

dtc
  • 1,774
  • 2
  • 21
  • 44