please don't rely on undocumented features of dal. this may work in a
database but not on another one, or may stop working from one web2py
release and not in another.
There's no support for substrings or slices of fields or even default
conversions between datetimes and string representations in select()s. You
can't concatenate fields on selects using | (don't know where you read it,
if you read it somewhere in the beginning), that is an operator to be used
only on arguments such as orderby or groupby.
tl,dr: there's no support for concatenation out of the box with dal, the
right way to do it is to fetch year,month,day,count as separate fields and
then concatenate in python to get to the representation you wish for. Or,
code your own select and execute it with executesql().
On Thursday, July 18, 2013 9:34:50 PM UTC+2, Alex W wrote:
>
> >>> 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.
