在SQLalchemy中Join日期只考虑日期而不考虑时间

Question

我需要在SQLalchemy中连接两个表的日期时间列，但我不想考虑完整的日期时间，而只想考虑它们的日期部分(连接具有相似日期的两行，即使这些天的时间不相等)。

这是我现在有的东西

import sqlalchemy as sq
...
first = meta.tables["schema.first"]
second = meta.tables["schema.second"]
select = sq.select([
    first.c.id,
    first.c.date,
    first.c.value,
    second.c.value])
    .select_from(first.join(second, sq.and_(
        first.c.id == second.c.id,
        first.c.date == second.c.date
        # exact match between datetimes is currently needed
    )))

Answer 1

在PostgreSQL中使用date_trunc(text, timestamp)函数将时间戳截断为日精度:

select = sq.select([first.c.id,
                    first.c.date,
                    first.c.value,
                    second.c.value])
    .select_from(first.join(second, sq.and_(
        first.c.id == second.c.id,
        sq.func.date_trunc('day', first.c.date) ==
        sq.func.date_trunc('day', second.c.date)
    )))

在这种情况下，您还可以将时间戳转换为日期:

sq.cast(first.c.date, sq.Date) == sq.cast(second.c.date, sq.Date)

Answer 2

from sqlalchemy.sql.functions import func
first = meta.tables("schema.first")
second = meta.tables("schema.second")
select = sq.select([
    first.c.id,
    first.c.date,
    first.c.value,
    second.c.value])
    .select_from(first.join(second, sq.and_(
        first.c.id == second.c.id,
        func.DATE(first.c.date) == func.DATE(second.c.date)
    )))