SQLAlchemy将纪元时间转换为分组日期 [英] Sqlalchemy convert epoch time to date in group by
问题描述
我正在使用Sqlalchemy作为PSQL数据库的ORM.我的时间戳记以纪元时间存储在我的数据库中,例如1525868337991.(以毫秒为单位)
I am using Sqlalchemy as ORM for PSQL db. My timestamps are stores as epoch times in my database eg, 1525868337991. (in milli sec)
我正在编写一个查询,以获取特定日期的雇员人数(按日期分组).我找不到任何方法可以在ORM查询中将 epoch转换为日期,例如psql具有 to_timestamp .该查询写在下面:
I am writing a query to get count of employees on a particular date(grouping by on date). I am not able to find any way by which, I can convert epoch to date in my ORM query, like psql has to_timestamp. The query is written below :
employees_details = db.session.query(
func.count(EmployeeInfo.id).label("employee_count"), EmployeeInfo.employee_created_on, EmployeeSourceInfo.employee_source_display_name
).join(
EmployeeSourceInfo, EmployeeInfo.lead_source_id == EmployeeSourceInfo.id
).group_by(func.as_utc(EmployeeInfo.employee_created_on), EmployeeSourceInfo.employee_source_display_name).all()
推荐答案
SQLAlchemy中的func
是通用的,可用于生成几乎所有SQL函数表达式.考虑到这一点,您可以简单地将func.as_utc
替换为
func.to_timestamp(EmployeeInfo.employee_created_on / 1000.0)
然后将其截断为一个日期,或者将其强制转换为一个日期:
To then truncate it to a date either cast it as one:
from sqlalchemy import Date
func.to_timestamp(EmployeeInfo.employee_created_on / 1000.0).cast(Date)
或使用Postgresql特定的函数date_trunc()
将生成的时间戳减少到日精度:
or use the Postgresql specific function date_trunc()
to reduce the resulting timestamp to day precision:
func.date_trunc('day', func.to_timestamp(EmployeeInfo.employee_created_on / 1000.0))
这篇关于SQLAlchemy将纪元时间转换为分组日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!