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.
