英文:
Postgresql change the timestamp to YYYYMMDD using to_char and proceed with the comparison via query in Fastapi
问题
You can modify the "days" variable to be an integer by removing the quotes around the format string. Here's the modified code:
days = int((datetime.datetime.now()).strftime("%Y%m%d"))
ex = session.execute("""SELECT * FROM tb WHERE num = %d AND TO_CHAR(updated_at, 'YYYYMMDD') >= %s""", (num, days))
This change should resolve the TypeError you mentioned.
英文:
Postgresql change the timestamp to YYYYMMDD using to_char and proceed with the comparison via query in Fastapi
num variable is already Int type.
days = (datetime.datetime.now()).strftime("%Y%m%d")
ex = session.execute("""SELECT * FROM tb WHERE num = %d AND TO_CHAR(updated_at, 'YYYYMMDD') >= %s""", (num, days))
error message
TypeError: '<' not supported between instances of 'str' and 'int'
How should I modify the days variable?
答案1
得分: 0
使用 to_date() 而不是将数据库值转换为本地格式，可以将本地格式转换为数据库的常规 date 类型：
days = (datetime.datetime.now()).strftime("%Y%m%d")
ex = session.execute("""SELECT *
FROM tb
WHERE num = %d
AND updated_at >= to_date(%s,'YYYYMMDD')
""", (num, days) )
如果您的数据库时间与客户端时间匹配，您也可以在查询中使用 now()，而不必在客户端检查、格式化并传递它。
然而，您的消息似乎指向其他地方，因为操作符 < 与查询中的 >= 和 = 不匹配，错误来自于Python而不是PostgreSQL。消息将不同，不匹配的类型将是 text，而不是 str。
英文:
Instead of converting the db value to your local format, you can use to_date() to convert that local format to db's regular date type:
days = (datetime.datetime.now()).strftime("%Y%m%d")
ex = session.execute("""SELECT *
FROM tb
WHERE num = %d
AND updated_at >= to_date(%s,'YYYYMMDD')
""", (num, days) )
If your database time matches your client time, you could also use now() in the query and not have to check, format and pass it from the client at all.
Still, your message seems to be pointing elsewhere, because the operator < doesn't match the >= and = in your query, and the error comes from Python, not PostgreSQL. The message would be different, and the mismatched type would be text, not str.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论