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:ti...@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),)
>
>
>>>> 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.
>
--
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.
