Why does AVG query not work after a month in Postgres?-CodePudding

I have been using this query in Postgres for a month, and it has been working fine:

SELECT
    DATE_TRUNC('hour', created_at::timestamp) AS datetime,
    AVG(temperature_1) as temperature_1
FROM
    main_data
WHERE
    created_at BETWEEN '2022-01-22 01:00:00' AND '2022-01-22 3:00:00'
GROUP BY 
    DATE_TRUNC('hour', created_at)

now when I use the query I get this error:

ERROR:  column "main_data.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: DATE_TRUNC('hour', created_at::timestamp) as datetime,
                           ^
SQL state: 42803
Character: 27

I have not done any update.

This the created_at field in my Postgres database

CodePudding user response：

When using group by keywords your column format (with the cast, etc) should same format in group by operation

SELECT
DATE_TRUNC('hour', created_at::timestamp) as datetime,
AVG(temperature_1) as temperature_1
FROM  main_data
where created_at BETWEEN '2022-01-22 01:00:00' AND '2022-01-22 3:00:00'
GROUP BY DATE_TRUNC('hour', created_at::timestamp)

CodePudding user response：

This would happen if the type of created_at changed to something which is not timestamp. For example, timestamptz.

Demonstration

The casting should not be necessary.