To clarify: slicing and concatenation of string fields is supported. The
problem is that in this case Alex is suggesting slicing datetime fields and
they happen to be strings on sqlite but not on other databases. One would
have to CAST the datetime to string before slicing and web2py does not
support a casting operator at this time.
Anyway, this kind of queries are expensive. Why not simply create a
db.define_table(..., Field('updated_date',compute =
compute_date_from_datetime(row.updated)))
and use db.updatelog.updated_date for the query?
On Thursday, 18 July 2013 19:43:31 UTC-5, Niphlod wrote:
>
> 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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
