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. On Sunday, September 22, 2013 2:58:03 AM UTC+2, Massimo Di Pierro wrote: > > 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) > > > > > > On Saturday, 21 September 2013 16:16:09 UTC-5, Ramos wrote: >> >> >> 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 >> >> >> No dia Sábado, 21 de Setembro de 2013, [email protected] escreveu: >> >>> 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 >>> >>> >>> >>> On Saturday, September 21, 2013 1:47:53 PM UTC+2, Ramos wrote: >>>> >>>> Hello, >>>> how can i count/group how many records were created in a 10 minutes >>>> period over a day? >>>> >>>> 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 [email protected]. >>> For more options, visit https://groups.google.com/groups/opt_out. >>> >> -- 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
