计算timestamp列小时均值报FROM keyword not found错误如何解决

错误原因 #

• 使用了Oracle保留关键字Date作为列别名，直接使用会触发语法解析错误，这是触发ORA-00923报错的核心原因
• 内层子查询仅返回了时间差字段intrvl，未向外层传递统计需要的endtime字段，导致外层trunc(endtime, 'HH')逻辑无法执行

修正后的SQL语句 #

select trunc(endtime, 'HH') as record_hour,
       avg(extract(second from intrvl)
           + extract(minute from intrvl) * 60
           + extract(hour from intrvl) * 60 * 60
           + extract(day from intrvl) * 60 * 60 * 24) as average
from (
    select endtime,
           (endtime - starttime) intrvl 
    from mytable
)
group by trunc(endtime, 'HH');

如果需要输出的时间格式和你提供的示例完全一致，可以用to_char做格式化并增加排序逻辑：

select to_char(trunc(endtime, 'HH'), 'dd/mm/yyyy HH24:MI') as record_hour,
       round(avg(extract(second from intrvl)
           + extract(minute from intrvl) * 60
           + extract(hour from intrvl) * 60 * 60
           + extract(day from intrvl) * 60 * 60 * 24), 3) as average
from (
    select endtime,
           (endtime - starttime) intrvl 
    from mytable
)
group by trunc(endtime, 'HH')
order by trunc(endtime, 'HH');

内容的提问来源于stack exchange，提问作者Tobitor