Maurits Mulders Maurits Mulders - 5 years ago 127
SQL Question

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

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

Answer Source

2 issues with your query:

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

  2. 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
    
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download