Trung tâm cá nhân
sách gpt4 ăn đã đi

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

In lại 作者：太空狗 更新时间：2023-10-29 20:22:22 26 4
mua khóa gpt4 giày nike

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

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

from sqlalchemy import Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
nhập ngày giờ

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)

kết quả:

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 是否以一种不寻常的方式参数化，这会使它们不适用于此类算术？

câu trả lời hay nhất

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

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

  • CHỮ 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.
  • SỐ NGUYÊN 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 ngày giờ 并将结果转换为秒。将 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),
]

关于python - 为什么这个查询会根据我如何安排 DateTime 算法给出不同的结果？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/49654885/

26 4 0
Đề xuất bài viết: c# - WPF PathGeometry 更新速度_SLOW_
Đề xuất bài viết: c++ - 如何在 size_t 上使用余数运算符获得负余数？
Đề xuất bài viết: Nhân viên nền C#
Đề xuất bài viết: python - 排序 python 3.7+ 字典的最快方法
太空狗
Hồ sơ

Tôi là một lập trình viên xuất sắc, rất giỏi!

Nhận phiếu giảm giá taxi Didi miễn phí
Phiếu giảm giá taxi Didi
Các bài viết phổ biến trên toàn bộ trang web
Chứng chỉ ICP Bắc Kinh số 000000
Hợp tác quảng cáo: 1813099741@qq.com 6ren.com
Xem sitemap của VNExpress