This error occurs when using a RANGE frame in a window function with a temporal ORDER BY column, but the frame bounds do not use INTERVAL syntax. MySQL requires INTERVAL expressions like "INTERVAL 5 DAY PRECEDING" for temporal columns.
The ER_WINDOW_RANGE_FRAME_TEMPORAL_TYPE error (error code 3588) is raised by MySQL when you use a RANGE-based window function frame with a temporal (date, time, timestamp, or datetime) column in the ORDER BY clause, but fail to specify the frame bounds using the INTERVAL syntax. MySQL window functions support two types of framing: ROWS (which counts physical row positions) and RANGE (which uses logical value ranges). When using RANGE frames with temporal data types, MySQL enforces a strict requirement: the frame bounds must be specified using INTERVAL expressions that match the temporal nature of the ORDER BY column. This design ensures type safety and prevents ambiguous frame specifications. For example, "5 PRECEDING" is meaningless for a date column—does it mean 5 days, 5 hours, or 5 months? The INTERVAL syntax removes this ambiguity by requiring an explicit time unit.
First, locate the query that is failing. Look for window functions (functions with OVER clause) that use RANGE frames and have an ORDER BY clause with a date, time, timestamp, or datetime column.
Example of problematic query:
SELECT
order_date,
amount,
SUM(amount) OVER(
ORDER BY order_date
RANGE BETWEEN 5 PRECEDING AND CURRENT ROW
) as rolling_sum
FROM orders;In this example, order_date is a temporal column, but the RANGE frame uses a numeric offset (5) without INTERVAL.
Update the RANGE frame specification to use INTERVAL syntax with an appropriate time unit. The syntax is INTERVAL value unit where unit can be DAY, HOUR, MINUTE, SECOND, WEEK, MONTH, YEAR, etc.
Fixed query:
SELECT
order_date,
amount,
SUM(amount) OVER(
ORDER BY order_date
RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND CURRENT ROW
) as rolling_sum
FROM orders;Common INTERVAL units:
- INTERVAL 1 DAY PRECEDING - Previous day
- INTERVAL 7 DAY PRECEDING - Previous week
- INTERVAL 1 MONTH PRECEDING - Previous month
- INTERVAL 1 HOUR PRECEDING - Previous hour
- INTERVAL '2:30' MINUTE_SECOND FOLLOWING - 2 minutes 30 seconds forward
Ensure your ORDER BY column is actually a temporal type (DATE, TIME, DATETIME, TIMESTAMP). If it's stored as a string or numeric type, you may need to convert it or restructure your query.
Check column type:
DESCRIBE orders;If the column is not temporal, either:
Option 1: Convert to temporal type in the query
SELECT
order_date,
amount,
SUM(amount) OVER(
ORDER BY STR_TO_DATE(order_date, '%Y-%m-%d')
RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND CURRENT ROW
) as rolling_sum
FROM orders;Option 2: Use ROWS instead of RANGE
SELECT
order_date,
amount,
SUM(amount) OVER(
ORDER BY order_date
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
) as rolling_sum
FROM orders;Note that ROWS counts physical row positions, not value ranges, so the semantics differ from RANGE.
Before running the full query, test with a minimal example to verify the syntax is correct:
SELECT
order_date,
amount,
COUNT(*) OVER(
ORDER BY order_date
RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW
) as day_count
FROM orders
LIMIT 10;Once this works, gradually add complexity (additional columns, more complex aggregations, etc.).
Understanding RANGE vs ROWS semantics: When using RANGE with temporal INTERVAL expressions, MySQL includes all rows whose ORDER BY value falls within the specified time interval of the current row's value. This differs from ROWS, which counts physical row positions regardless of values. For example, if you have multiple orders on the same date, RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW includes all orders from the previous day through all orders on the current date (even if there are 100 rows with the same date), while ROWS BETWEEN 1 PRECEDING AND CURRENT ROW only includes the immediately preceding row plus the current row.
Frame exclusion options: MySQL 8.0.2 and later support frame exclusion clauses like EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, and EXCLUDE NO OTHERS which can be combined with RANGE frames to fine-tune which rows are included in the window.
Multiple interval units: For time-of-day columns, you can use compound intervals like INTERVAL '2:30' MINUTE_SECOND for 2 minutes and 30 seconds, or INTERVAL '1:30' HOUR_MINUTE for 1 hour and 30 minutes. Refer to the MySQL documentation on DATE_ADD() function for all supported interval formats.
Performance considerations: RANGE frames with temporal expressions can be more expensive than ROWS frames because MySQL must evaluate the temporal arithmetic for each row. If your use case allows it and you only need a fixed number of preceding/following rows, consider using ROWS instead. However, for time-based rolling calculations (e.g., "sum of sales in the last 7 days"), RANGE with INTERVAL is the correct choice.
MySQL version compatibility: The RANGE frame with temporal INTERVAL support was introduced in MySQL 8.0. If you are using MySQL 5.7 or earlier, window functions are not supported at all. Upgrade to MySQL 8.0 or later to use this feature.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions