Hi Angelo,
On Jul 15, 12:12 pm, Angelo Compagnucci <[email protected]>
wrote:
> Hi Denes,
>
> thank you for your time!
>
> row.data.date() could be the solution to the problem, but it forces me
> to traverse the table returned by the query and build another table
> with dates instead of datetimes... Not the best solution in speed and
> elegance!
>
> I think that a query like the one I made it's really common. I have a
> timestamp and I want to count the occurences of a determined event by
> date exctracted by the timestamp. I think that excrating the date (or
> the time) from a datetime it's really a speedy operation if
> accomplished by the database, and should be supported by all major
> databases ( I think really all databases!).
>
> By the way I resolved with this quick and dirty hack:
>
> rows = db(db.test).select(db.test.data[:10])
that "dirty hack" fails for the MSSQL adapter in 1.97.1:
>>> rr=db2(db2.test).select(db2.test.data[:10])
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "C:\w2p\web2py_1.97.1_src\gluon\dal.py", line 5394, in select
return self.db._adapter.select(self.query,fields,attributes)
File "C:\w2p\web2py_1.97.1_src\gluon\dal.py", line 1176, in select
rows = response(sql)
File "C:\w2p\web2py_1.97.1_src\gluon\dal.py", line 1166, in response
self.execute(sql)
File "C:\w2p\web2py_1.97.1_src\gluon\dal.py", line 1251, in execute
return self.log_execute(*a, **b)
File "C:\w2p\web2py_1.97.1_src\gluon\dal.py", line 1246, in
log_execute
ret = self.cursor.execute(*a,**b)
ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server
Driver][SQL Ser
ver]Argument data type datetime is invalid for argument 1 of substring
function.
(8116) (SQLExecDirectW)')
the SQL command issued is:
>>> db2._lastsql
'SELECT SUBSTRING(test.data,1,(11 - 1)) FROM test WHERE (test.id >
0);'
in the case of SQLite the command is:
>>> db._lastsql
'SELECT SUBSTR(test.data,1,(11 - 1)) FROM test WHERE (test.id > 0);'
so you see it all depends on the DB backend, and we would have to
check if all the other DBs do have such functions.
The MSSQL error could be a bug.
Also note how the content of each row differs from a 'standard'
select:
>>> rr=db(db.test).select(db.test.data)
>>> print rr[0]
<Row {'data': datetime.datetime(2011, 7, 15, 14, 34, 18)}>
>>> rr=db(db.test).select(db.test.data[:10])
>>> print rr[0]
<Row {'_extra': <Row {'SUBSTR(test.data,1,(11 - 1))': u'2011-07-15'}>}
>
>
> slicing the first 10 character is enough for me!
>
> If anyoune intrested, I'll explore te possibility to add a .date() and
> a .time() to FIeld object, or find a way to concatenate .year()
> .month() .day()!
>
> Thank you!
