@paolo: You can't mix & match python with SQL and expect consistent results. @massimo: there's a problem: they'll never be consistent among databases. Some db return timerange, some number of days, some number of seconds, when you subtract two dates without involving functions. that's one of the reasons we introduced the epoch() method.
Il giorno venerdì 28 giugno 2013 13:23:56 UTC+2, Paolo valleri ha scritto: > > 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
