MySql; how do you find time between two queries and sort them by least time

Question

I have the following question to answer:

For each tag, retrieve the 10 to-dos with quickest completion time (i.e. the time between the creation and the completion of the to-do)

I tried this

Select *

From ToDoItem, Tag, ItemTag

Where 

    time between CreationDate and 

    CompletionDate is ASC AND

    Tag.Id = ItemTag.ToDoId AND 

    ItemTag.TagId = ToDoItem.Id

Limit 10

So I first want to find the time between the creation date and completion date and after that I want them asc so it sorts them om time getting more, after that I select all the tags and at last I want to limit them to max 10.

But this doesn't work and I think the problem in somewhere in the "Time between

creationdate

and

CompletionDate

".

So the question I have is the following:

How do you find the time between two different tables with dates in it?

Description of table

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

2 issues with your query:

You need to use datediff() function to get the time between.

Ordering the results ahould be done with order by statement, not in the where part.

Select *
From ToDoItem, Tag, ItemTag
Where   
  Tag.Id = ItemTag.ToDoId AND 
  ItemTag.TagId = ToDoItem.Id
Order by datediff(completiontime, creationtime) asc
Limit 10

MySql; how do you find time between two queries and sort them by least time

Sign up