sqlalchemy_datebetween.py

from sqlalchemy.sql import expression
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import Date, DateTime


class DateBetween(expression.FunctionElement):

    type = Date
    name = 'datebetween'


@compiles(DateBetween, 'postgresql')
def case_datebetween(element, compiler, **kw):

    column, start, end = list(element.clauses)

    '''
    if all([isinstance(start.type, element.type), isinstance(end.type, element.type)]):
        _clause += '{0} >= {1} and {2} <= {3}'.format(
            compiler.process(column),
            compiler.process(start),
            compiler.process(column),
            compiler.process(end)
        )

    elif all([isinstance(start.type, element.type), not isinstance(end.type, element.type)]):
        _clause += '{0} >= {1}'.format(compiler.process(column), compiler.process(start))

    elif all([not isinstance(start.type, element.type), isinstance(end.type, element.type)]):
        _clause += '{0} <= {1}'.format(compiler.process(column), compiler.process(end))
    '''

    _clause = '%s between coalesce(%s, %s) and coalesce(%s, %s)' % (
        compiler.process(column),
        compiler.process(start),
        compiler.process(column),
        compiler.process(end),
        compiler.process(column)
    )

    return _clause