Hi Massimo, can you point out how to write query with date diff? I tried this: db(diff_date > timedelta(days=4)).select() and I got the same ticket, I don't know if this is there right way for expressing query with date_diff.
Paolo On Friday, June 28, 2013 12:41:13 PM UTC+2, Massimo Di Pierro wrote: > > Please open a ticket about this. Expressions like date_diff were designed > to be in the query, not in the list of selected columns. I think this can > be done but need to check what breaks. > > On Friday, 28 June 2013 03:02:06 UTC-5, Paolo valleri wrote: >> >> Dear all, >> I am trying to run simple mathematical operations with datetime and >> postgres as backend ( >> http://www.postgresql.org/docs/9.0/static/functions-datetime.html) >> I've prepared this simple example code: >> from datetime import timedelta >> db.define_table('test_sum', >> Field('first_int', 'integer'), >> Field('second_int', 'integer'), >> Field('first_date', 'datetime'), >> Field('second_date', 'datetime'), >> ) >> >> db.test_sum.insert(first_int=40, second_int=30, first_date=request.now, >> second_date=request.now-timedelta(days=1)) >> diff_int = db.test_sum.first_int - db.test_sum.second_int >> diff_date = db.test_sum.first_date - db.test_sum.second_date >> print db(db.test_sum).select(diff_int) >> print db(db.test_sum).select(diff_date) >> With postgres the first query is executed correctly, the second failed >> raising the ticket: >> >> Traceback (most recent call last): >> File "/home/pvalleri/src/web2py/gluon/restricted.py", line 212, in >> restricted >> exec ccode in environment >> File "/home/pvalleri/src/web2py/applications/vtraffic/models/db.py" >> <http://127.0.0.1:8000/admin/edit/vtraffic/models/db.py>, line 98, in >> <module> >> print db(db.test_sum).select(diff_date) >> File "/home/pvalleri/src/web2py/gluon/dal.py", line 9935, in select >> return adapter.select(self.query,fields,attributes) >> File "/home/pvalleri/src/web2py/gluon/dal.py", line 1709, in select >> return self._select_aux(sql,fields,attributes) >> File "/home/pvalleri/src/web2py/gluon/dal.py", line 1690, in _select_aux >> return processor(rows,fields,self._colnames,cacheable=cacheable) >> File "/home/pvalleri/src/web2py/gluon/dal.py", line 2093, in parse >> fields[j].type,blob_decode) >> File "/home/pvalleri/src/web2py/gluon/dal.py", line 1908, in parse_value >> return self.parsemap[key](value,field_type) >> File "/home/pvalleri/src/web2py/gluon/dal.py", line 1995, in parse_double >> return float(value) >> TypeError: float() argument must be a string or a number >> >> >> To some extent the interesting ticket is that, the query generated works >> without problems (tested with pgadmin). >> SELECT (test_sum.second_date - test_sum.first_date) FROM test_sum WHERE ( >> test_sum.id IS NOT NULL); >> >> Moreover, I run the same example with sqlite, it worked well without >> error, the resulting values are wrong though. >> What can I do? >> >> Paolo >> >> -- --- 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.
