On Dec 20, 2010, at 8:22 AM, ellonweb wrote:
> If I have an integer column I can easily select the column minus one:
> session.query(mytable.column - 1)
>
> If I want to select a datetime column minus one minute, there doesn't
> seem to be an easy way to do it.
> I would have expected to be to do something like:
> session.query(mytable.column - datetime.timedelta(minutes=1))
>
> The only way I've been able to do what I want is like this:
> session.query(mytable.column - cast('60', Interval))
> Is this the best way to do this or have I missed something?
This depends on the DBAPI and database backend in use. For example, if you use
psycopg2 with postgresql, you can pretty much pass in timedeltas and datetimes
and date arithmetic is fully possible (SQLAlchemy 0.6 needed). With other
backends such as SQLIte and SQL Server, you typically need to use the built-in
functions of those backends to coerce timedeltas into integer values and/or use
the comparison functions provided by that backend.
Some modicum of platform-neutrality can be achieved if you use the @compiles
extension to build higher level date functions that do what you need, such as
the date comparison function below I use for PG/MSSQL:
from sqlalchemy import expression, Integer
from sqlalchemy.ext.compiler import compiles
class datediff(expression.FunctionElement):
type = Integer()
name = 'datediff'
@compiles(datediff, 'postgresql')
def _pg_datediff(element, compiler, **kw):
return "(%s::date - %s::date)" % (
compiler.process(element.clauses.clauses[1]),
compiler.process(element.clauses.clauses[0]),
)
@compiles(datediff, 'mssql')
def _ms_datediff(element, compiler, **kw):
return "DATEDIFF(day, %s, %s)" % (
compiler.process(element.clauses.clauses[0], **kw),
compiler.process(element.clauses.clauses[1], **kw),
)
>
> Thanks
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
