Django MySQL Query - Select DateTime 60 minutes Larger Then Previous Row?

Question

I have a model that has datetime filed called "start_time".

Lets say I do basic select query:

results = MyModel.objects.filter(start_time=somedate).order_by('start_time')

I will get something like:

2015-07-10 17:15:00
2015-07-10 19:15:00
2015-07-10 19:45:00
2015-07-10 21:15:00
2015-07-10 21:45:00

My goal is to exclude all rows that are not at least 60 minutes larger then the previous row so I would get this result:

2015-07-10 17:15:00
2015-07-10 19:15:00
2015-07-10 21:15:00

Any suggestions?

I'm not quite sure how would I use the `group by`. Can you collaborate little bit more on that idea? — WayBehind, Jul 24 '15 at 16:29
http://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django. This will help you. — Vishnu Upadhyay, Jul 24 '15 at 16:41
Thanks for the link. Again, how would the `group_by` help to eliminate those rows that are not `60 minutes` larger than the previous row? Any suggestions? — WayBehind, Jul 24 '15 at 17:00
`group by` pick just single value from each hour if it has. but if a date like `2015-07-10 17:15:00` and `2015-07-10 18:14:00`. Group by pick 2nd one too because it's single a hit of another hour. — Vishnu Upadhyay, Jul 24 '15 at 17:03
Also, what if I need 90 minutes over the previous row? OR just 45 minutes? — WayBehind, Jul 24 '15 at 17:10
You solution was technically correct as I was asking for 60min. Few upvotes your way ans thanks for the effort! — WayBehind, Jul 24 '15 at 19:01

Lutfar Rahman Milu · Accepted Answer · 2015-07-24T17:20:47.583

1

The corresponding sql query might look like this:

SELECT * FROM Table
WHERE 
  start_time > somedate
GROUP BY 
  DATE(start_time), 
  HOUR(start_time)

I'm not sure if django ORM supports this kind of GROUP BY. If not you may try raw sql.

Edit: The 60 minute or 1 hour is covered up by grouping it by date-hour.It then takes the first element of each group. If you are talking about 90 minutes or anything that doesn't fit into datetime field then I'm afraid , this approach will not work. The filtering should be done manually.

edited Jul 24 '15 at 17:20

answered Jul 24 '15 at 16:42

Lutfar Rahman Milu

121
8

Thank you for the feedback! I'm not sure how to understand your answer as I do not see any way to benefit from this query as I'm not sure where exactly is your query excluding the all the rows that are not `60 minutes` larger than the previous row. Can you collaborate? – WayBehind Jul 24 '15 at 16:58
OK this seems to be working, now the question is. What if I need 90 minutes over the previous row? – WayBehind Jul 24 '15 at 17:10
Thank you for the update and sorry about the confusion! I just put in 60min as an example without realizing that a different time will change the answer. – WayBehind Jul 24 '15 at 17:23
I have accepted your answer as technically your solution was correct. I will have to ask another question to get the 90 min version. Thanks again for your help! – WayBehind Jul 24 '15 at 19:00

Django MySQL Query - Select DateTime 60 minutes Larger Then Previous Row?

1 Answers1