support for timedeltas as operators on datetime columns

[ellonweb]

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?

Thanks

[Michael Bayer]

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 sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[ellonweb]

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...

[Michael Bayer]

On Dec 20, 2010, at 11:58 AM, ellonweb wrote:

> 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())

can't reproduce, psycopg2 2.2.2:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from datetime import *

Base = declarative_base()

engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

class Updates(Base):
__tablename__ = 'updates'

id = Column(Integer, primary_key=True)
timestamp = Column(DateTime, default=func.current_timestamp())

Base.metadata.create_all(engine)

session = Session(engine)
session.add_all([Updates(), Updates(), Updates()])
session.commit()

td=timedelta(minutes=1)
print Updates.timestamp - td

print session.query(Updates.timestamp - td).first()

output:

2010-12-20 12:22:04,686 INFO sqlalchemy.engine.base.Engine.0x...7510 select version()
2010-12-20 12:22:04,686 INFO sqlalchemy.engine.base.Engine.0x...7510 {}
2010-12-20 12:22:04,688 INFO sqlalchemy.engine.base.Engine.0x...7510 select current_schema()
2010-12-20 12:22:04,688 INFO sqlalchemy.engine.base.Engine.0x...7510 {}
2010-12-20 12:22:04,690 INFO sqlalchemy.engine.base.Engine.0x...7510 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
2010-12-20 12:22:04,690 INFO sqlalchemy.engine.base.Engine.0x...7510 {'name': u'updates'}
2010-12-20 12:22:04,731 INFO sqlalchemy.engine.base.Engine.0x...7510 BEGIN (implicit)
2010-12-20 12:22:04,731 INFO sqlalchemy.engine.base.Engine.0x...7510 INSERT INTO updates (timestamp) VALUES (CURRENT_TIMESTAMP) RETURNING updates.id
2010-12-20 12:22:04,732 INFO sqlalchemy.engine.base.Engine.0x...7510 {}
2010-12-20 12:22:04,759 INFO sqlalchemy.engine.base.Engine.0x...7510 INSERT INTO updates (timestamp) VALUES (CURRENT_TIMESTAMP) RETURNING updates.id
2010-12-20 12:22:04,759 INFO sqlalchemy.engine.base.Engine.0x...7510 {}
2010-12-20 12:22:04,760 INFO sqlalchemy.engine.base.Engine.0x...7510 INSERT INTO updates (timestamp) VALUES (CURRENT_TIMESTAMP) RETURNING updates.id
2010-12-20 12:22:04,760 INFO sqlalchemy.engine.base.Engine.0x...7510 {}
2010-12-20 12:22:04,761 INFO sqlalchemy.engine.base.Engine.0x...7510 COMMIT
updates.timestamp - :timestamp_1
2010-12-20 12:22:04,763 INFO sqlalchemy.engine.base.Engine.0x...7510 BEGIN (implicit)
2010-12-20 12:22:04,764 INFO sqlalchemy.engine.base.Engine.0x...7510 SELECT updates.timestamp - %(timestamp_1)s AS anon_1
FROM updates
LIMIT 1 OFFSET 0
2010-12-20 12:22:04,764 INFO sqlalchemy.engine.base.Engine.0x...7510 {'timestamp_1': datetime.timedelta(0, 60)}
(datetime.datetime(2010, 12, 20, 12, 21, 4, 732161),)

[ellonweb]

>>> print session.query(Updates.timestamp - td).first()
2010-12-20 17:44:45,757 INFO sqlalchemy.engine.base.Engine.0x...dad0
BEGIN (implicit)
2010-12-20 17:44:45,766 INFO sqlalchemy.engine.base.Engine.0x...dad0

SELECT updates.timestamp - %(timestamp_1)s AS anon_1
FROM updates
LIMIT 1 OFFSET 0

2010-12-20 17:44:45,779 INFO sqlalchemy.engine.base.Engine.0x...dad0
{'timestamp_1': datetime.timedelta(0, 60)}
Traceback (most recent call last):
...

I'm using psycopg2 2.0.14, I'm starting to think that's the cause?
Slightly off-topic but do you know if the binary distributions of
psycopg2 built against pg9 will work fine on 8.4? (This is the reason
I'm using such an old version)

Here's the full traceback if it's useful: http://paste.pound-python.org/show/740/

[ellonweb]

I've updated to psycopg2 2.2.1, working now. Sorry for wasting your
time!