count record created in a 10 minutes interval

[António Ramos]

Hello, 

how can i count/group how many records were created in a 10 minutes period over a day?

Thank you

[Niphlod]

example needed .... do you want 6*24=144 different groups each one holding a "slice" of 10 minutes with the number of records in it, given a day?

i.e.
1 --> 00:00 to 00:09 --> 3 records
2 --> 00:10 to 00:19 --> 4 records
....
144 --> 23:50 to 23:59 --> 1 record

[António Ramos]

For simplicity I can accept a query of records saved within an hour.so only 24 slots.

I know that I create at least one record for every 10 minutes.

 I want to create a scale for every hour.

If I detect 5 or 6 records created I draw a green cell, in the other hand zero records is bad and I create a red cell. In between I can create a gradient from green to red.

How do I query my db for  the 24 slots?

Thank you 

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Massimo Di Pierro]

There is no web2py API for this because, to my knowledge, almost none of the supported database can do it.

Web2py supports Vertica (http://www.vertica.com/). Vertica can do this:

def fpbts(field1,field2,field3,minutes=5):

    return Expression(field1.db,"FIRST_VALUE(%s) OVER (PARTITION BY TIME_SLICE(%s, %s, 'MINUTE','END')+%s)" % (field1,field2,minutes,field3))

rows = db().select(fpbts(db.table.field1, db.table.field2, minutes=60)

Thank you 

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.

[Niphlod]

ehm, actually is a pretty common, perfectly solvable in a relational db. The point in web2py is that there are only hours, minutes, seconds, so 10 minutes is not achievable by web2py without passing a custom expression (that should be like "datepart(minutes, the_datetime) / 10" )

However, if the requirement is by the hour ....

count = db.table.id.count()
groupby = db.table.the_datetime.hour()
db(db.table.the_datetime < tomorrow 00:00)(db.table.the_datetime > today 00:00).select(count, groupby, groupby=groupby)

NB: you **should** expect less slots than 24 if there is no record in a particular slot.

BTW, if the requirement is for 10 minutes, you can use either executesql or group by hour AND minute and then squash out in python the 10 minutes slots together.
If the maximum timeframe is 24 hours, it means a loop at most over 1440 elements, so pretty fast.