TIMESTAMPDIFF(DAY, created_time, complete_time) AS daysTemp, Error: SQL0206N "DAY" 在使用它的上下文中无效。 SQLSTATE=42703 (State:S0022, Native Code: FFFFFF32)

这个错误的原因是在您的SQL语句中使用了不支持的数据类型或者函数。可能是因为您的数据库版本不支持此函数或数据类型，或者您的语法有误。

在这个例子中，可能是您的数据库不支持用 DAY 作为 TIMESTAMPDIFF 函数的第一个参数。您可以尝试将 DAY 改为对应于您数据库支持的时间单位，如 HOUR 或 MINUTE。另外，请确保您的SQL语句语法正确，没有其他的错误。

相关问题

将hour(COMPLETE_TIME)- hour(created_time)修改为TIMESTAMPDIFF的方式

假设`created_time`和`COMPLETE_TIME`是两个DATETIME类型的列，可以使用MySQL的内置函数TIMESTAMPDIFF来计算它们之间的时间差，单位为小时。具体实现方式如下：

SELECT TIMESTAMPDIFF(HOUR, created_time, COMPLETE_TIME) AS hour_diff
FROM your_table;

其中，第一个参数指定时间差的单位，这里是HOUR；第二个参数是被减数，即`created_time`；第三个参数是减数，即`COMPLETE_TIME`。该查询语句会返回一个名为`hour_diff`的列，其中每一行的值表示相应记录中`created_time`和`COMPLETE_TIME`之间的时间差，单位为小时。

select distinct exam_id, duration, release_time from (select exam_id as exam_id, duration, release_time, sum(case when rank1 = 2 then costtime when rank2 = 2 then -costtime else 0 end) as sub from ( select e_i.exam_id, duration, release_time, timestampdiff(minute, start_time, submit_time) as costtime, row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) desc) rank1, row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) asc) rank2 from exam_record e_r join examination_info e_i on e_r.exam_id = e_i.exam_id where submit_time is not null ) table1 group by exam_id ) table2 where sub * 2 >= duration order by exam_id desc;把这段sql改写成oracle语法

SELECT DISTINCT exam_id, duration, release_time
FROM (
SELECT exam_id, duration, release_time, SUM(CASE WHEN rank1 = 2 THEN costtime WHEN rank2 = 2 THEN -costtime ELSE 0 END) AS sub
FROM (
SELECT e_i.exam_id, duration, release_time, TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS costtime,
ROW_NUMBER() OVER(PARTITION BY e_r.exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) DESC) rank1,
ROW_NUMBER() OVER(PARTITION BY e_r.exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) ASC) rank2
FROM exam_record e_r JOIN examination_info e_i
ON e_r.exam_id = e_i.exam_id
WHERE submit_time IS NOT NULL
) table1
GROUP BY exam_id, duration, release_time
) table2
WHERE sub * 2 >= duration
ORDER BY exam_id DESC;