CODEWITHSUNDEEP
mysql
Jonathan
Jonathan

Reputation: 1936

Unable to use 'where' when using 'timestampdiff' in mySQL

I am trying to execute the following function. 

%%sql SELECT Dog_Guid, start_time, end_time, TIMESTAMPDIFF(MINUTE, start_time, end_time) AS Duration
from exam_answers
where Duration > 0
order by duration desc
limit 10

And I am producing this error:

(_mysql_exceptions.OperationalError) (1054, "Unknown column 'Duration' in 'where clause'") [SQL: 'SELECT Dog_Guid, start_time, end_time, TIMESTAMPDIFF(MINUTE, start_time, end_time) AS Duration\nfrom exam_answers\nwhere Duration > 0\norder by duration desc\nlimit 10']

Is this because I can't use where on an alias or because I can't use where on derived columns? I don't see what I'm doing wrong here. What I'd like to do is just output all the columns that are 0 are greater (the column does have null values also, hence...).

Upvotes: 2

Views: 675

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

You can't use an alias in where clause 

SELECT Dog_Guid, start_time, end_time, TIMESTAMPDIFF(MINUTE, start_time, end_time) AS Duration
from exam_answers
 where TIMESTAMPDIFF(MINUTE, start_time, end_time) > 0
 order by duration desc
 limit 10

the where clause is processed before than the select clause .. so at the moment of where processing the alias is not know

For details, see the manual "Problems with Column Aliases".

Upvotes: 3

Related Questions