On Jun 28, 2013, at 4:20 PM, Łukasz Fidosz <[email protected]> wrote:
> 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.
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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
