>>> rows = db(db.updatelog).select(db.updatelog.updated[:10],
db.updatelog.cnt.sum(),
groupby=db.updatelog.updated.year()|db.updatelog.updated.month()|db.updatelog.updated.day())
>>> print rows
"SUBSTR(updatelog.updated,1,(11 - 1))",SUM(updatelog.cnt)
2013-07-12 00:00:00,12
2013-07-13 00:00:00,14
2013-07-14 00:00:00,17
2013-07-15 00:00:00,21
2013-07-16 00:00:00,24
2013-07-17 00:00:00,26
2013-07-18 00:00:00,29
I suppose this is close enough
On Thursday, July 18, 2013 10:35:54 AM UTC-7, Alex W wrote:
>
> I'm seeing unexpected results when trying to groupby and concatenate
> datetime fields;
>
> >>> db.define_table('updatelog',
> ... Field('sourcename','string'),
> ... Field('firstseen','datetime'),
> ... Field('lastseen','datetime'),
> ... Field('cnt','integer'),
> ... Field('updated','datetime')
> ... )
> <Table updatelog (id,sourcename,firstseen,lastseen,cnt,updated)>
> >>> db.updatelog.insert(sourcename="1", firstseen="2013-07-18 14:34:59",
> lastseen="2013-07-18 14:34:59", updated="2013-07-18 14:34:59", cnt="20")
> 1L
> >>> db.updatelog.insert(sourcename="2", firstseen="2013-07-18 12:34:59",
> lastseen="2013-07-18 12:34:59", updated="2013-07-18 12:34:59", cnt="9")
> 2L
> >>> db.updatelog.insert(sourcename="1", firstseen="2013-07-17 12:34:59",
> lastseen="2013-07-17 12:34:59", updated="2013-07-17 12:34:59", cnt="26")
> 3L
> >>> db.updatelog.insert(sourcename="1", firstseen="2013-07-17 11:34:59",
> lastseen="2013-07-17 12:34:59", updated="2013-07-16 12:34:59", cnt="24")
> 4L
> >>> db.updatelog.insert(sourcename="4", firstseen="2013-07-17 11:34:59",
> lastseen="2013-07-17 12:34:59", updated="2013-07-15 12:34:59", cnt="21")
> 5L
> >>> db.updatelog.insert(sourcename="1", firstseen="2013-07-14 12:34:59",
> lastseen="2013-07-14 12:34:59", updated="2013-07-14 12:34:59", cnt="17")
> 6L
> >>> db.updatelog.insert(sourcename="1", firstseen="2013-07-13 11:34:59",
> lastseen="2013-07-13 12:34:59", updated="2013-07-13 12:34:59", cnt="14")
> 7L
> >>> db.updatelog.insert(sourcename="1", firstseen="2013-07-12 10:34:59",
> lastseen="2013-07-12 12:34:59", updated="2013-07-12 12:34:59", cnt="12")
> 8L
> >>> print
> db(db.updatelog).select(db.updatelog.updated.year()|db.updatelog.updated.month()|db.updatelog.updated.day(),
>
> db.updatelog.cnt.sum(),
> groupby=db.updatelog.updated.year()|db.updatelog.updated.month()|db.updatelog.updated.day())
> "web2py_extract('year',updatelog.updated),
> web2py_extract('month',updatelog.updated),
> web2py_extract('day',updatelog.updated)",SUM(updatelog.cnt)
> 2013,7
> 2013,7
> 2013,7
> 2013,7
> 2013,7
> 2013,7
> 2013,7
> >>> db._lastsql
> "SELECT web2py_extract('year',updatelog.updated),
> web2py_extract('month',updatelog.updated),
> web2py_extract('day',updatelog.updated), SUM(updatelog.cnt) FROM updatelog
> WHERE (updatelog.id IS NOT NULL) GROUP BY
> web2py_extract('year',updatelog.updated),
> web2py_extract('month',updatelog.updated),
> web2py_extract('day',updatelog.updated);"
>
>
> What I'd like is the output to look *similar* to this, but with the dates
> concatenated together in one column, as '2013-07-12', etc..
> >>> print
> db(db.updatelog).select(db.updatelog.updated.year(),db.updatelog.updated.month(),db.updatelog.updated.day(),
>
> db.updatelog.cnt.sum(),
> groupby=db.updatelog.updated.year()|db.updatelog.updated.month()|db.updatelog.updated.day())
>
> "web2py_extract('year',updatelog.updated)","web2py_extract('month',updatelog.updated)","web2py_extract('day',updatelog.updated)",SUM(updatelog.cnt)
> 2013,7,12,12
> 2013,7,13,14
> 2013,7,14,17
> 2013,7,15,21
> 2013,7,16,24
> 2013,7,17,26
> 2013,7,18,29
>
> >>>
>
>
>
>
--
---
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.
