This has everything to do with the database driver in use and the database itself. Acceptance of a Python timedelta object by the DBAPI and proper interpretation of it against the result of a TIMEDIFF call (which is MySQL specific) is not a given on most DBAPIs. For cases like these your best bet is to first look at all SQL being emitted, and try to stick to foolproof types like ints as far as what you pass to the database. As for SQLAlchemy, we're just passing SQL strings and Python constructs to cursor.execute().
On Nov 18, 2010, at 5:04 AM, Christian Démolis wrote: > Hi, > > I found a usecase that is illogical > > Here is the first code and his result in term. All is False > > milieu = model.aliased(model.Plage) > s2 = model.session.query( > milieu, > model.func.timediff(milieu.Fin, milieu.Debut), > model.func.timediff(milieu.Fin, > milieu.Debut)>datetime.timedelta(minutes=self.duree_cible) > ) > for e in s2: > print e > > (Plage(589L,'L',datetime.datetime(2010, 11, 16, 8, 0),datetime.datetime(2010, > 11, 16, 8, 10)), datetime.timedelta(0, 600), False) > (Plage(590L,'L',datetime.datetime(2010, 11, 16, 14, > 0),datetime.datetime(2010, 11, 16, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(591L,'L',datetime.datetime(2010, 11, 22, 8, 0),datetime.datetime(2010, > 11, 22, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(592L,'L',datetime.datetime(2010, 11, 22, 14, > 0),datetime.datetime(2010, 11, 22, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(593L,'L',datetime.datetime(2010, 11, 23, 8, 0),datetime.datetime(2010, > 11, 23, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(594L,'L',datetime.datetime(2010, 11, 23, 14, > 0),datetime.datetime(2010, 11, 23, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(595L,'L',datetime.datetime(2010, 11, 29, 8, 0),datetime.datetime(2010, > 11, 29, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(596L,'L',datetime.datetime(2010, 11, 29, 14, > 0),datetime.datetime(2010, 11, 29, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(597L,'L',datetime.datetime(2010, 11, 30, 8, 0),datetime.datetime(2010, > 11, 30, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(598L,'L',datetime.datetime(2010, 11, 30, 14, > 0),datetime.datetime(2010, 11, 30, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(599L,'L',datetime.datetime(2010, 12, 6, 8, 0),datetime.datetime(2010, > 12, 6, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(600L,'L',datetime.datetime(2010, 12, 6, 14, 0),datetime.datetime(2010, > 12, 6, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(601L,'L',datetime.datetime(2010, 12, 7, 8, 0),datetime.datetime(2010, > 12, 7, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(602L,'L',datetime.datetime(2010, 12, 7, 14, 0),datetime.datetime(2010, > 12, 7, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(603L,'L',datetime.datetime(2010, 12, 13, 8, 0),datetime.datetime(2010, > 12, 13, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(604L,'L',datetime.datetime(2010, 12, 13, 14, > 0),datetime.datetime(2010, 12, 13, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(605L,'L',datetime.datetime(2010, 12, 14, 8, 0),datetime.datetime(2010, > 12, 14, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(606L,'L',datetime.datetime(2010, 12, 14, 14, > 0),datetime.datetime(2010, 12, 14, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(607L,'L',datetime.datetime(2010, 12, 20, 8, 0),datetime.datetime(2010, > 12, 20, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(608L,'L',datetime.datetime(2010, 12, 20, 14, > 0),datetime.datetime(2010, 12, 20, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(609L,'L',datetime.datetime(2010, 12, 21, 8, 0),datetime.datetime(2010, > 12, 21, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(610L,'L',datetime.datetime(2010, 12, 21, 14, > 0),datetime.datetime(2010, 12, 21, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(611L,'L',datetime.datetime(2010, 12, 27, 8, 0),datetime.datetime(2010, > 12, 27, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(612L,'L',datetime.datetime(2010, 12, 27, 14, > 0),datetime.datetime(2010, 12, 27, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(613L,'L',datetime.datetime(2010, 12, 28, 8, 0),datetime.datetime(2010, > 12, 28, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(614L,'L',datetime.datetime(2010, 12, 28, 14, > 0),datetime.datetime(2010, 12, 28, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(615L,'L',datetime.datetime(2011, 1, 3, 8, 0),datetime.datetime(2011, > 1, 3, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(616L,'L',datetime.datetime(2011, 1, 3, 14, 0),datetime.datetime(2011, > 1, 3, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(617L,'L',datetime.datetime(2011, 1, 4, 8, 0),datetime.datetime(2011, > 1, 4, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(618L,'L',datetime.datetime(2011, 1, 4, 14, 0),datetime.datetime(2011, > 1, 4, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(619L,'L',datetime.datetime(2011, 1, 10, 8, 0),datetime.datetime(2011, > 1, 10, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(620L,'L',datetime.datetime(2011, 1, 10, 14, 0),datetime.datetime(2011, > 1, 10, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(621L,'L',datetime.datetime(2011, 1, 11, 8, 0),datetime.datetime(2011, > 1, 11, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(622L,'L',datetime.datetime(2011, 1, 11, 14, 0),datetime.datetime(2011, > 1, 11, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(623L,'L',datetime.datetime(2011, 1, 17, 8, 0),datetime.datetime(2011, > 1, 17, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(624L,'L',datetime.datetime(2011, 1, 17, 14, 0),datetime.datetime(2011, > 1, 17, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(625L,'L',datetime.datetime(2011, 1, 18, 8, 0),datetime.datetime(2011, > 1, 18, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(626L,'L',datetime.datetime(2011, 1, 18, 14, 0),datetime.datetime(2011, > 1, 18, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(627L,'L',datetime.datetime(2011, 1, 24, 8, 0),datetime.datetime(2011, > 1, 24, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(628L,'L',datetime.datetime(2011, 1, 24, 14, 0),datetime.datetime(2011, > 1, 24, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(629L,'L',datetime.datetime(2011, 1, 25, 8, 0),datetime.datetime(2011, > 1, 25, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(630L,'L',datetime.datetime(2011, 1, 25, 14, 0),datetime.datetime(2011, > 1, 25, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(631L,'L',datetime.datetime(2011, 1, 31, 8, 0),datetime.datetime(2011, > 1, 31, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(632L,'L',datetime.datetime(2011, 1, 31, 14, 0),datetime.datetime(2011, > 1, 31, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(633L,'L',datetime.datetime(2011, 2, 1, 8, 0),datetime.datetime(2011, > 2, 1, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(634L,'L',datetime.datetime(2011, 2, 1, 14, 0),datetime.datetime(2011, > 2, 1, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(635L,'L',datetime.datetime(2010, 11, 16, 9, > 20),datetime.datetime(2010, 11, 16, 10, 0)), datetime.timedelta(0, 2400), > False) > (Plage(636L,'O',datetime.datetime(2010, 11, 16, 8, > 10),datetime.datetime(2010, 11, 16, 9, 20)), datetime.timedelta(0, 4200), > False) > > > > Here is the second code and results in term. I just change inequality and it > works. > milieu = model.aliased(model.Plage) > s2 = model.session.query( > milieu, > model.func.timediff(milieu.Fin, milieu.Debut), > model.func.timediff(milieu.Fin, > milieu.Debut)-datetime.timedelta(minutes=self.duree_cible)>0 > ) > for e in s2: > print e > > > (Plage(589L,'L',datetime.datetime(2010, 11, 16, 8, 0),datetime.datetime(2010, > 11, 16, 8, 10)), datetime.timedelta(0, 600), False) > (Plage(590L,'L',datetime.datetime(2010, 11, 16, 14, > 0),datetime.datetime(2010, 11, 16, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(591L,'L',datetime.datetime(2010, 11, 22, 8, 0),datetime.datetime(2010, > 11, 22, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(592L,'L',datetime.datetime(2010, 11, 22, 14, > 0),datetime.datetime(2010, 11, 22, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(593L,'L',datetime.datetime(2010, 11, 23, 8, 0),datetime.datetime(2010, > 11, 23, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(594L,'L',datetime.datetime(2010, 11, 23, 14, > 0),datetime.datetime(2010, 11, 23, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(595L,'L',datetime.datetime(2010, 11, 29, 8, 0),datetime.datetime(2010, > 11, 29, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(596L,'L',datetime.datetime(2010, 11, 29, 14, > 0),datetime.datetime(2010, 11, 29, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(597L,'L',datetime.datetime(2010, 11, 30, 8, 0),datetime.datetime(2010, > 11, 30, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(598L,'L',datetime.datetime(2010, 11, 30, 14, > 0),datetime.datetime(2010, 11, 30, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(599L,'L',datetime.datetime(2010, 12, 6, 8, 0),datetime.datetime(2010, > 12, 6, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(600L,'L',datetime.datetime(2010, 12, 6, 14, 0),datetime.datetime(2010, > 12, 6, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(601L,'L',datetime.datetime(2010, 12, 7, 8, 0),datetime.datetime(2010, > 12, 7, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(602L,'L',datetime.datetime(2010, 12, 7, 14, 0),datetime.datetime(2010, > 12, 7, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(603L,'L',datetime.datetime(2010, 12, 13, 8, 0),datetime.datetime(2010, > 12, 13, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(604L,'L',datetime.datetime(2010, 12, 13, 14, > 0),datetime.datetime(2010, 12, 13, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(605L,'L',datetime.datetime(2010, 12, 14, 8, 0),datetime.datetime(2010, > 12, 14, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(606L,'L',datetime.datetime(2010, 12, 14, 14, > 0),datetime.datetime(2010, 12, 14, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(607L,'L',datetime.datetime(2010, 12, 20, 8, 0),datetime.datetime(2010, > 12, 20, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(608L,'L',datetime.datetime(2010, 12, 20, 14, > 0),datetime.datetime(2010, 12, 20, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(609L,'L',datetime.datetime(2010, 12, 21, 8, 0),datetime.datetime(2010, > 12, 21, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(610L,'L',datetime.datetime(2010, 12, 21, 14, > 0),datetime.datetime(2010, 12, 21, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(611L,'L',datetime.datetime(2010, 12, 27, 8, 0),datetime.datetime(2010, > 12, 27, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(612L,'L',datetime.datetime(2010, 12, 27, 14, > 0),datetime.datetime(2010, 12, 27, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(613L,'L',datetime.datetime(2010, 12, 28, 8, 0),datetime.datetime(2010, > 12, 28, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(614L,'L',datetime.datetime(2010, 12, 28, 14, > 0),datetime.datetime(2010, 12, 28, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(615L,'L',datetime.datetime(2011, 1, 3, 8, 0),datetime.datetime(2011, > 1, 3, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(616L,'L',datetime.datetime(2011, 1, 3, 14, 0),datetime.datetime(2011, > 1, 3, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(617L,'L',datetime.datetime(2011, 1, 4, 8, 0),datetime.datetime(2011, > 1, 4, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(618L,'L',datetime.datetime(2011, 1, 4, 14, 0),datetime.datetime(2011, > 1, 4, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(619L,'L',datetime.datetime(2011, 1, 10, 8, 0),datetime.datetime(2011, > 1, 10, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(620L,'L',datetime.datetime(2011, 1, 10, 14, 0),datetime.datetime(2011, > 1, 10, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(621L,'L',datetime.datetime(2011, 1, 11, 8, 0),datetime.datetime(2011, > 1, 11, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(622L,'L',datetime.datetime(2011, 1, 11, 14, 0),datetime.datetime(2011, > 1, 11, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(623L,'L',datetime.datetime(2011, 1, 17, 8, 0),datetime.datetime(2011, > 1, 17, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(624L,'L',datetime.datetime(2011, 1, 17, 14, 0),datetime.datetime(2011, > 1, 17, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(625L,'L',datetime.datetime(2011, 1, 18, 8, 0),datetime.datetime(2011, > 1, 18, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(626L,'L',datetime.datetime(2011, 1, 18, 14, 0),datetime.datetime(2011, > 1, 18, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(627L,'L',datetime.datetime(2011, 1, 24, 8, 0),datetime.datetime(2011, > 1, 24, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(628L,'L',datetime.datetime(2011, 1, 24, 14, 0),datetime.datetime(2011, > 1, 24, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(629L,'L',datetime.datetime(2011, 1, 25, 8, 0),datetime.datetime(2011, > 1, 25, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(630L,'L',datetime.datetime(2011, 1, 25, 14, 0),datetime.datetime(2011, > 1, 25, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(631L,'L',datetime.datetime(2011, 1, 31, 8, 0),datetime.datetime(2011, > 1, 31, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(632L,'L',datetime.datetime(2011, 1, 31, 14, 0),datetime.datetime(2011, > 1, 31, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(633L,'L',datetime.datetime(2011, 2, 1, 8, 0),datetime.datetime(2011, > 2, 1, 10, 0)), datetime.timedelta(0, 7200), True) > (Plage(634L,'L',datetime.datetime(2011, 2, 1, 14, 0),datetime.datetime(2011, > 2, 1, 16, 0)), datetime.timedelta(0, 7200), True) > (Plage(635L,'L',datetime.datetime(2010, 11, 16, 9, > 20),datetime.datetime(2010, 11, 16, 10, 0)), datetime.timedelta(0, 2400), > True) > (Plage(636L,'O',datetime.datetime(2010, 11, 16, 8, > 10),datetime.datetime(2010, 11, 16, 9, 20)), datetime.timedelta(0, 4200), > True) > > Is it normal???? > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
