本文介绍了使用SQLAlchemy计算POSTGRES中的DATEDIFF的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要在几分钟内计算 DATEDIFF 在时间戳类型的两列之间。 网上有很多简单的示例,但是使用psycopg2 + sqlalchemy时,它们都无法正常工作。 我尝试过:
I need to calculate DATEDIFF in minutes between 2 columns of timestamp type. There are so many simple examples on the web, but none of them work really much properly using psycopg2 + sqlalchemy. I've tried:
from sqlalchemy import as sa from datetime import datetime # con is a standard pool of connections :class:Connection con.execute( sa.func.datediff( sa.literal_column('minute'), datetime.utcnow(), datetime.utcnow(), ) )它抛出:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "minute" does not exist LINE 1: SELECT datediff(minute, '2017-02-27T15:04:33.217559'::timest... ^ [SQL: 'SELECT datediff(minute, %(datediff_2)s, %(datediff_3)s) AS datediff_1'] [parameters: {'datediff_3': datetime.datetime(2017, 2, 27, 15, 4, 33, 217596), 'datediff_2': datetime.datetime(2017, 2, 27, 15, 4, 33, 217559)}]如果我尝试:
con.execute( sa.func.datediff( 'minute', datetime.utcnow(), datetime.utcnow(), ) )我收到:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) function datediff(unknown, timestamp without time zone, timestamp without time zone) does not exist LINE 1: SELECT datediff('minute', '2017-02-27T12:27:49.369724'::time... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. [SQL: 'SELECT datediff(%(datediff_2)s, %(datediff_3)s, %(datediff_4)s) AS datediff_1'] [parameters: {'datediff_4': datetime.datetime(2017, 2, 27, 12, 27, 49, 369740), 'datediff_2': 'minute', 'datediff_3': datetime.datetime(2017, 2, 27, 12, 27, 49, 369724)}]任何想法如何使其正确?
Any ideas how to make it right?
推荐答案PostgreSQL没有 datediff 函数。要获取分钟数,请使用SQL表达式:
trunc((extract(epoch FROM newer_date) - extract(epoch FROM older_date)) / 60)
- extract(epoch FROM…) 将时间戳转换为秒数。
- / 60 转换为秒到分钟
- trunc (…)除去小数部分。
- extract(epoch FROM …) converts the timestamp to number of seconds.
- / 60 converts to seconds to minutes
- trunc(…) removes the fractional part.
所以可能尝试
sa.func.trunc(( sa.extract('epoch', datetime.utcnow()) - sa.extract('epoch', datetime.utcnow()) ) / 60)更多推荐
使用SQLAlchemy计算POSTGRES中的DATEDIFF
发布评论