FYI, I'm using 0.6.5 and postgre8.4/psycopg2.
Passing in timedeltas didn't work, though I notice the opposite works:
I can pass in a datetime to compare to a db-datetime and SQLA gives me
a timedelta back, but I can't compare a timedelta with a db-datetime
to get a datetime back:
With a declarative table Updates, containing a column timestamp,
roughly like this:
class Updates(Base):
__tablename__ = 'updates'
timestamp = Column(DateTime, default=current_timestamp())
>>> td=timedelta(minutes=1)
>>> td
datetime.timedelta(0, 60)
>>> Updates.timestamp - td
<sqlalchemy.sql.expression._BinaryExpression object at 0x033D5150>
>>> print Updates.timestamp - td
updates.timestamp - %(timestamp_1)s
>>> session.query(Updates.timestamp - td).first()
sqlalchemy.exc.DataError: (DataError) invalid input syntax for type
timestamp: "0 days 60.000000 seconds"
LINE 1: SELECT updates.timestamp - '0 days 60.000000 seconds' AS
ano...
On Dec 20, 3:02 pm, Michael Bayer <[email protected]> wrote:
> 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
> > athttp://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.
