SQLAlchemy 添加将整数列转换为分钟间隔的日期时间列

Question

假设有这样的内容：

column_a = Column(Datetime,...)
column_b = Column(Integer)

数据库类型 - postgresql

我该如何写一个像这样的查询呢？

SELECT * from table WHERE column_a + interval '{column_b value} minute' > now()

Answer 1

我之前也遇到过类似的问题，找不到把整数转换成PostgreSQL中的时间间隔的方法。 我通过把列的类型从整数改成时间间隔（INTERVAL）来解决这个问题，像这样：

from datetime import datetime, timedelta
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, DateTime, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.dialects.postgresql import INTERVAL
from config import DB_CONFIG

Base = declarative_base()


class Model(Base):

    __tablename__ = 'model'
    id = Column(Integer, primary_key=True)
    column_a = Column(DateTime)
    column_b = Column(INTERVAL)


engine = create_engine(
    'postgresql+psycopg2://%s:%s@%s:%d/%s'
    % (DB_CONFIG["user"], DB_CONFIG["password"], DB_CONFIG["host"], 
       DB_CONFIG["port"], DB_CONFIG["db_name"]),
    echo=True
)
Session = sessionmaker(bind=engine)
session = Session()

Base.metadata.create_all(engine)

# INTERVAL type can be set via instance of timedelta
session.add(Model(column_a=datetime.now(), column_b=timedelta(minutes=10)))
session.add(Model(
    column_a=datetime.now() - timedelta(minutes=20),
    column_b=timedelta(minutes=10)
))
session.commit()

result = session.query(Model).filter(
    Model.column_a + Model.column_b > datetime.now()
).all()

for row in result:
    print row.id

第一次运行后的输出结果是：1