SQL: CAST combined with window function causes syntax error

Question

I want to condense my database, which now contains a large number of values, to 1/12 of the original size by averaging twelve consecutive values.

To do this, I form the mean value using the types TIMESTAMP and FLOAT(4,2). In order to average the TIMESTAMP properly, I first wanted to cast the TIMESTAMP into an INT, then calculate the average value with AVG() and finally cast the average value back into a DATETIME. During this operation, however, I get a syntax error that I cannot explain.

That is the respective query I've tried:

INSERT INTO condensed_12_current(time_mean, current_mean)
SELECT
  CAST(AVG(CAST(time as INTEGER)) AS DATETIME) OVER (
    ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ) AS time_mean,
  AVG(current) OVER (
    ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ) AS current_mean
FROM current
WHERE id % 12 = 0;

Caused Error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER)) AS DATETIME) OVER (
    ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ' at line 3

I have tried many different versions of the CAST instruction, but none of them have produced the desired result. The only CAST statement that was syntactically correct was when I cast the AVG() value back to the original data type.

Table current has the following structure: time as TIMESTAMP current as FLOAT(4,2) id as INT AUTO INCREMENT

Table condensed_12_current has the following structure: time as TIMESTAMP current as FLOAT(4,2)

MYSQL Ver 8.2.0 for Linux on x86_64 (MySQL Community Server - GPL)

Answer 1

You should tell your database how to order the rows or it will process them in arbitrary order. For example, to order rows by the ID column:

avg(current) over (
    order by id rows between 11 preceding and current row
    ) as current_mean

Example at DB Fiddle

MySQL doesn't seem to support casting a timestamp to an integer. You can use from_unixtime() and unix_timestamp() instead:

from_unixtime(avg(unix_timestamp(ts)) over
            (rows between 1 preceding and current row))

Example at DB Fiddle