Hourly average for timestamps columns - FROM keyword not found where expected-CodePudding

I would like to get the hourly average of two columns. The type of the columns is timestamp.

I try this:

select trunc(endtime, 'HH') as Date,
    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 - starttime) intrvl 
    from mytable
)
group by Date;

I do not know if this is the right way to go at all. But the query is not correct. I get this error: 00923. 00000 - "FROM keyword not found where expected"

In the end I would like to get a table like this one:

Date average
16/11/2021 08:00 5.786
16/11/2021 09:00 8.996

How can I get this?

CodePudding user response：

You need to include all the columns you are going to use in the outer-query in the SELECT clause of the inner-query and you cannot use an alias of the SELECT clause in the GROUP BY clause in the same sub-query:

select trunc(endtime, 'HH') as "Date",
       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 AS intrvl 
  from   mytable
)
group by trunc(endtime, 'HH');

If you do not have (or are not worried about) fractional seconds then you could also use:

select trunc(endtime, 'HH') as "Date",
       AVG(
         (CAST(endtime AS DATE) - CAST(starttime AS DATE))
         * 60 * 60 * 24
       ) AS average
from   mytable
group by trunc(endtime, 'HH');

db<>fiddle here

CodePudding user response：

Date is reserved word (for a datatype), you can't use it as an alias unless you enclose it into double quotes as

  SELECT TRUNC (endtime, 'HH') AS "Date",
         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 - starttime) intrvl FROM mytable)
GROUP BY "Date";

Alternatively, rename it to e.g. c_date

SELECT TRUNC (endtime, 'HH') AS c_date,