python - 为什么这个查询会根据我如何安排 DateTime 算法给出不同的结果？

Question

我已经使用 SqlAlchemy 创建了一个表，Record。每条记录都有一个字段date，它存储一个DateTime。我想查找日期晚于八小时前的所有记录。

我想出了四种编写过滤器的方法，所有方法都涉及比较当前时间、记录时间和八小时时间增量的简单算术。问题是:这些过滤器中有一半返回八小时窗口之外的行。

from sqlalchemy import Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
import datetime

Base = declarative_base()

class Record(Base):
    __tablename__ = 'record'
    id = Column(Integer, primary_key=True)
    date = Column(DateTime, nullable=False)

engine = create_engine('sqlite:///records.db')
Base.metadata.create_all(engine)
DBSession = sessionmaker(bind=engine)
session = DBSession()

#if the db is empty, add some records to the database with datetimes corresponding to one year ago and one hour ago and yesterday
now = datetime.datetime(2018, 4, 4, 10, 0, 0)
if not session.query(Record).all():
    session.add(Record(date = now - datetime.timedelta(days=365)))
    session.add(Record(date = now - datetime.timedelta(days=1)))
    session.add(Record(date = now - datetime.timedelta(hours=1)))


delta = datetime.timedelta(hours=8)

#these are all equivalent to "records from the last eight hours"
criterion = [
    (now - Record.date < delta),
    (Record.date > now - delta),
    (delta > now - Record.date),
    (now - delta < Record.date),
]

for idx, crit in enumerate(criterion):
    query = session.query(Record).filter(crit)
    print("\n\nApproach #{}.".format(idx))
    print("Generated statement:")
    print(query.statement)
    records = query.all()
    print("{} row(s) retrieved.".format(len(records)))
    for record in query.all():
        print(record.id, record.date)

结果:

Approach #0.
Generated statement:
SELECT record.id, record.date
FROM record
WHERE :date_1 - record.date < :param_1
3 row(s) retrieved.
1 2017-04-04 10:00:00
2 2018-04-03 10:00:00
3 2018-04-04 09:00:00


Approach #1.
Generated statement:
SELECT record.id, record.date
FROM record
WHERE record.date > :date_1
1 row(s) retrieved.
3 2018-04-04 09:00:00


Approach #2.
Generated statement:
SELECT record.id, record.date
FROM record
WHERE :date_1 - record.date < :param_1
3 row(s) retrieved.
1 2017-04-04 10:00:00
2 2018-04-03 10:00:00
3 2018-04-04 09:00:00


Approach #3.
Generated statement:
SELECT record.id, record.date
FROM record
WHERE record.date > :date_1
1 row(s) retrieved.
3 2018-04-04 09:00:00

方法 1 和 3 是正确的 - 它们返回一小时前的记录，而不是一天前或一年前的记录。方法 0 和方法 2 是不正确的，因为它们除了返回一个小时前的记录外，还返回了一天前的记录和一年前的记录。

造成这种差异的原因是什么？我注意到 #1 和 #3 生成的语句仅参数化单个 datetime 对象，而 #0 和 #2 参数化 datetime 对象和 timedelta 对象。 timedeltas 是否以一种不寻常的方式参数化，这会使它们不适用于此类算术？

Answer 1

As noted by unutbu ，当 timedelta 对象用作不支持 native Interval 的数据库的绑定(bind)参数时类型，它们将转换为相对于“纪元”(1970 年 1 月 1 日)的时间戳。 SQLite 就是这样一个数据库，MySQL 也是。 .当您打开日志记录时，另一个值得注意的事情是 datetime 值为 stored and passed as ISO formatted strings .

A DATETIME column has NUMERIC affinity在 SQLite 中，但由于 ISO 格式的字符串不能无损地转换为数值，因此它们保留了它们的 TEXT 存储类。另一方面这很好，因为 3 ways to store date and time data在 SQLite 中是

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

不过，当您尝试在数据库中执行算术运算时，事情会变得更有趣:

In [18]: session.execute('SELECT :date_1 - record.date FROM record',
    ...:                 {"date_1": now}).fetchall()
2018-04-04 20:47:35,045 INFO sqlalchemy.engine.base.Engine SELECT ? - record.date FROM record
INFO:sqlalchemy.engine.base.Engine:SELECT ? - record.date FROM record
2018-04-04 20:47:35,045 INFO sqlalchemy.engine.base.Engine (datetime.datetime(2018, 4, 4, 10, 0),)
INFO:sqlalchemy.engine.base.Engine:(datetime.datetime(2018, 4, 4, 10, 0),)
Out[18]: [(1,), (0,), (0,)]

原因是all mathematical operators cast their operands to NUMERIC storage class ，即使结果值是有损的——或者就此而言没有意义。在这种情况下，年份部分被解析，其余部分被忽略。

自 any INTEGER or REAL value is less与任何 TEXT 或 BLOB 值相比，结果整数值与给定 ISO 格式的区间字符串之间的所有比较都为真:

In [25]: session.execute(text('SELECT :date_1 - record.date < :param_1 FROM record')
    ...:                 .bindparams(bindparam('param_1', type_=Interval)),
    ...:                 {"date_1": now, "param_1": delta}).fetchall()
    ...:                 
2018-04-04 20:55:36,952 INFO sqlalchemy.engine.base.Engine SELECT ? - record.date < ? FROM record
INFO:sqlalchemy.engine.base.Engine:SELECT ? - record.date < ? FROM record
2018-04-04 20:55:36,952 INFO sqlalchemy.engine.base.Engine (datetime.datetime(2018, 4, 4, 10, 0), '1970-01-01 08:00:00.000000')
INFO:sqlalchemy.engine.base.Engine:(datetime.datetime(2018, 4, 4, 10, 0), '1970-01-01 08:00:00.000000')
Out[25]: [(1,), (1,), (1,)]

有些人可能将这一切称为有漏洞的抽象，但在 SQLAlchemy 中为数据库实现之间的所有差异提供解决方案将是一项艰巨的任务，或者说是不可能完成的任务。就我个人而言，我发现它不会妨碍使用，但允许按原样使用数据库的功能，但有一个很好的 Python DSL。如果您确实需要在单个代码库中支持不同数据库中的时间差异，请创建一个 custom construct使用合适的特定于数据库的编译器。

要实际计算 SQLite 中的差异并与给定 timedelta 中的总秒数进行比较，您 need to use the strftime()函数，以便将 ISO 格式的字符串转换为自纪元以来的秒数。 julianday()也可以，只要您也转换 Python datetime 并将结果转换为秒。将 2 个行为不当的比较替换为例如:

# Not sure if your times were supposed to be UTC or not
now_ts = now.replace(tzinfo=datetime.timezone.utc).timestamp()
delta_s = delta.total_seconds()

# Not quite pretty...
criterion = [
    (now_ts - func.strftime('%s', Record.date) < delta_s,
    (Record.date > now - delta),
    (delta_s > now_ts - func.strftime('%s', Record.date)),
    (now - delta < Record.date),
]