[sqlalchemy] Re: support for timedeltas as operators on datetime columns
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.00 seconds
LINE 1: SELECT updates.timestamp - '0 days 60.00 seconds' AS
ano...
On Dec 20, 3:02 pm, Michael Bayer mike...@zzzcomputing.com 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: support for timedeltas as operators on datetime columns
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.00 seconds
LINE 1: SELECT updates.timestamp - '0 days 60.00 seconds' AS
ano...
On Dec 20, 3:02 pm, Michael Bayer mike...@zzzcomputing.com 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):
[sqlalchemy] Re: support for timedeltas as operators on datetime columns
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/
On Dec 20, 5:24 pm, Michael Bayer mike...@zzzcomputing.com wrote:
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.00 seconds
LINE 1: SELECT updates.timestamp - '0 days 60.00 seconds' AS
ano...
On Dec 20, 3:02 pm, Michael Bayer mike...@zzzcomputing.com 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 -
[sqlalchemy] Re: support for timedeltas as operators on datetime columns
I've updated to psycopg2 2.2.1, working now. Sorry for wasting your
time!
On Dec 20, 6:01 pm, ellonweb ellon...@gmail.com wrote:
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/
On Dec 20, 5:24 pm, Michael Bayer mike...@zzzcomputing.com wrote:
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.00 seconds
LINE 1: SELECT updates.timestamp - '0 days 60.00 seconds' AS
ano...
On Dec 20, 3:02 pm, Michael Bayer mike...@zzzcomputing.com 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
