BinaryExpression wrong type (datetime instead of date)

[Tom Kedem]

Suppose I have the following arrangement:

class MyModel(Base):
    date = Column(Date)

    @hybrid_property
    def offset_date(self):
        return self.date + datetime.timedelta(days=1)

The offset_date type is datetime instead of date. I would expect the result of the BinaryExpression to evaluate to the same type of the column. This is in accordance to how python evaluates the addition:

>>> type(datetime.date.today() + datetime.timedelta(days=1))
<class 'datetime.date'>

I'm using the latest master to date.

[Mike Bayer]

Hello -

I appreciate that this is not the Python datetime behavior, however these expressions are intended to be interpreted as SQL. In SQL, date + interval, at least on the best platform we know for dates (Postgresql), returns a datetime:

test=> select '2015-02-15'::date + '1 day'::interval;
      ?column?       
---------------------
 2015-02-16 00:00:00
(1 row)

test=> select '2015-02-15'::date + '1 day 25 seconds'::interval;
      ?column?       
---------------------
 2015-02-16 00:00:25
(1 row)

therefore I suggest there's no action to be taken here.

[Tom Kedem]

I understand where you're coming from. But I use the hybrid property as a means of querying the date field. The query returns an empty result set since it receives the wrong type (which is inferred from the hybrid property). I don't think this is a wanted behavior. Maybe having some kind of mechanism to interpret the addition differently when converting to SQL/Python? Or maybe adding the ability to specify a hint for the type, or some kind of conversion?

Also, if I add an expression:

@offset_date.expression
def offset_date(cls):
    return cls.date + datetime.timedelta(days=1)

I will get different return types for the instance and class variations. This can be easily solved by using a conversion for the instance method, but it's pitfall.

[Mike Bayer]

OK so at this point what's lacking for me is any context or examples, because SQLAlchemy does not in fact produce datetime.date() or datetime.datetime() objects at all, unless you're using SQLite and we're talking about SQL expressions - but I'm pretty sure the SQL expression in question here won't even work on any backend except Postgresql; all the backends have very different date arithmetic behavior and only Postgresql really handles basic things like dates + intervals. For other backends you need to use special functions to coerce dates and such.

So when you say "the query receives the wrong type", I can only guess we're talking about SQL like this:

SELECT * FROM table WHERE date + somedelta = some_other_date

and perhaps because you get a datetime with hours/minutes/seconds on it, the comparison doesn't happen, but if the delta is really "1 day" then that wouldn't happen, so really, I don't know how you're getting this issue. For any subsequent response, please provide an mcve so that I know what we're talking about.

The short answer though is that while SQLAlchemy knows that "date + timedelta = datetime", it doesn't enforce that; if this is Postgresql, that's your database making that interpretation. If you want a DATETIME to be interpreted as a DATE, you'd use CAST. The expression here would look like:

cast(cls.date + datetime.timedelta(days=1), Date)

You probably need to be making use of cast() here, but again, please provide specifics as to what backend this is, what the SQL you're emitting looks like, and why you get "no results", thanks!

[Tom Kedem]

Hey, Thanks for the elaborate explanation.

Using the cast in the expression gives the desired type consistently. While It's possible to argue which should be the default type (date/datetime) I could live with that.

I'm using sqlite, and it seems what I thought didn't work because of date/datetime conversions, actually was sqlite not handling date arithmetics the same way the SQL is produced:

2016-02-19 17:30:55,667 INFO sqlalchemy.engine.base.Engine SELECT foo.id AS foo_id, foo.date AS foo_date 
FROM foo 
WHERE foo.date + ? = ?
2016-02-19 17:30:55,671 INFO sqlalchemy.engine.base.Engine ('1970-01-02 00:00:00.000000', '2016-02-20')

timedelta translates to epoch + timedelta, not really the desired result. As you said, sqlite does in fact uses different date arithmetics: https://www.sqlite.org/lang_datefunc.html

Ideally sqlalchemy should produce a different sql for different backends. I suppose this in particular is something not yet supported? Do you have plans to support that? Any particular workarounds I could use in this case?

[Mike Bayer]

So the "handle every datetime system" has been a long standing "nice to have" in #2769, there's no detail there but it would be a really big and elaborate system since date arithmetic is intricate and is completely different on every backend. The general idea for now is to use the compilation extension http://docs.sqlalchemy.org/en/rel_1_0/core/compiler.html# to come up with the constructs you need. I thought there was an example of date subtraction across PG and SQL Server but apparently not, so here is that as an example:

class _sql_age_in_days(expression.FunctionElement):
    type = Integer()
    name = 'age'

@compiles(_sql_age_in_days, 'postgresql')
def _pg_age_in_days(element, compiler, **kw):
    # there's other ways to do this, but using the
    # way that returns the same result as SQL Server
    return "(%s::date - %s::date)" % (
        compiler.process(element.clauses.clauses[1]),
        compiler.process(element.clauses.clauses[0]),
    )

@compiles(_sql_age_in_days, 'mssql')
def _ms_age_in_days(element, compiler, **kw):
    return "DATEDIFF(day, %s, %s)"  % (
            compiler.process(element.clauses.clauses[0], **kw),
            compiler.process(element.clauses.clauses[1], **kw),
        )

getting that expression to return the exact same value on PG and SQL Server in all cases was really difficult, in fact. For SQLite you'd want to do something similar using their epoch-related functions.

[Tom Kedem]

Thanks a lot. This is what I've come up with and it seems to work:

from datetime import timedelta
from sqlalchemy.sql.functions import GenericFunction
from sqlalchemy import Date, func

class date(GenericFunction):
    type = Date


class Date(Date):
    class comparator_factory(Date.Comparator):
        def __add__(self, other):
            if isinstance(other, timedelta):
                args = []
                for value_type in ('days', 'seconds', 'microseconds'):
                    value = getattr(other, value_type)
                    if value:
                        if value_type == 'microseconds':
                            # TODO warn user that microseconds are not supported by sqlite
                            continue
                        args.append('{value} {value_type}'.format(value=value, value_type=value_type))
                return func.date(self.expr, *args)
            return super().__add__(other)

I would actually like to have a pull request for that, but I need to:

1. Make sure it will only compile that way for sqlite. I tried to convert the code to your suggest format and use FunctionElement and @compiles but was kinda lost. I'll try some more when I have the time.

2. Figure out where this code goes inside sqlalchemy? overriding the __add__ function is easy enough, but I couldn't find it's definition with all the factories in the way.

3. Any other thoughts you have?

[Mike Bayer]

The above code might be fine for your needs and I suggest you go with it, but for inclusion in SQLAlchemy it would have to be a lot more generalized than that, the feature would need to work for all general date arithmetic operations across all dialects and input types, including other SQL expression as well as literals, need tons of tests, etc. The conduits for dealing with literals and doing the equivalent of "isinstance(timestamp)" are also established already in very different ways than the above approach.

It's not a good idea to hardcode an extremely specific sliver of a use case in the middle of a system that otherwise doesn't provide such a set of features; it's better to leave open the areas for calling applications to implement the specific recipes they need, as you've done for your application here.

[Tom Kedem]

I understand. Thank you very much for your help and responsiveness!

[Tom Kedem]

Won't fix - see comments for details.