TypeError: unsupported operand type(s) for -: 'float' and 'datetime.datetime' when subtracting DateTime columns directly with MySQL database

[Łukasz Fidosz]

Hi,

When subtracting DateTime columns in query, like: session.query((Foo.datetime1 - Foo.datetime2).label('diff')) SQLAlchemy tries to handle result as timedelta but MySQL returns float as a result so it crashes with "TypeError: unsupported operand type(s) for -: 'float' and 'datetime.datetime'" exception.

So I wondering if it's a bug or it should never be queried like that and proper function for subtracting datetimes should be always used instead?

Tested on fresh cloned from repository SQLAlchemy version.

Example code is attached to this message.

[Michael Bayer]

its a missing feature right now. ideally we can add a rule to MySQL's datetime object that subtraction should return a type that will translate the float to an Interval (I'm guessing it's a number of days).

You can get the float right now like this:

from sqlalchemy import type_coerce
session.query((type_coerce(Foo.date1 - Foo.date2, Float)).label('diff')).all()


Or if on 0.8, a more comprehensive workaround like this:

from sqlalchemy import type_coerce, Float, TypeDecorator

class MySQLInterval(TypeDecorator):
impl = Float

def process_result_value(self, value, dialect):
# guessing, seems to be close
return datetime.timedelta(days=value / 100 / 60 / 60 / 24)

class MySQLDateTime(TypeDecorator):
impl = DateTime

class comparator_factory(TypeDecorator.Comparator):
def __sub__(self, other):
return type_coerce(type_coerce(self.expr, DateTime) - other, MySQLInterval)


Using MySQLDateTime will then treat the result of a __sub__() as a float to convert into a timedelta.

[Łukasz Fidosz]

Thanks:) I previously used cast instead of types_coerce for my use case.

[Charlie Clark]

Am 28.06.2013, 22:45 Uhr, schrieb Michael Bayer <mik...@zzzcomputing.com>:

> its a missing feature right now. ideally we can add a rule to MySQL's
> datetime object that subtraction should return a type that will
> translate the float to an Interval (I'm guessing it's a number of days).

FWIW the MySQL function timediff might be appropriate:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226