将AVG从HH-MM-SS转换为分钟

Question

我有一个这样的查询：

with first_reply as (
select 
t.id as ticket_id,
tc.created,
row_number() over(partition by tc.ticket_id order by tc.created) as rn
from ticket t
inner join ticket_comment tc
on t.id = tc.ticket_id
inner join user u 
on u.id = tc.user_id
where u.role in ('employee')
order by tc.ticket_id, tc.created)
select 
avg(far.created-t.created_at)
from ticket t
inner join first_agent_reply far
on far.ticket_id = t.id
join ticket_tag tt
ON tt.ticket_id = t.id
AND rn = 1

它给了我一个22:23:56.973609 格式的平均时间

如何将其转换为仅显示总分钟数？即：1920分钟

谢谢！

Answer 1

如果您使用的是sql server，请使用以下函数

DATEDIFF(minute, 0, CONVERT(TIME,'22:23:56.973609'))

可能你代码的最后一部分是这样的，

select 
DATEDIFF(minute, 0, CONVERT(TIME,avg(far.created-t.created_at)))
from ticket t

你在这里得到的答案是1343