CODEWITHSUNDEEP
mysql
Zsolt János
Zsolt János

Reputation: 511

Mysql timediff with min

I'm making a query to monitor ongoing and expecting file uploads. Here is what i'm making:

select time_to_sec(min(timediff(now(), f.SubmittedDate)))/60 FROM dbtable f WHERE f.Status = 'SUBMITTED';

Now, after 12 hours, this keeps returning 10 times more then it should. Instead of 620 minutes, it returns 6200 minutes. If i do it this way, it works:

select time_to_sec(timediff(now(), max(f.SubmittedDate)))/60 FROM dbtable f WHERE f.Status = 'SUBMITTED'

I don't understand the difference, why is this happening? Obviously, the lowest timediff should be equal to the timediff between now and the highest date. Am i missing something or is this a bug?

My problem is that i have loads of checks set up this way and editing all of them could be a fair share amount of work.

Thanks for any help!

Upvotes: 1

Views: 5358

Answers (1)

Simon Thornett
Simon Thornett

Reputation: 56

I ran some tests on this and your code:

select time_to_sec(min(timediff(now(), f.SubmittedDate)))/60 FROM dbtable f WHERE f.Status = 'SUBMITTED';

Needs to be:

select min(time_to_sec(timediff(now(), f.SubmittedDate)))/60 FROM dbtable f WHERE f.Status = 'SUBMITTED';

As you aren't using the "MAX" function like in your second query you need to call the "MIN" function before the "TIME_TO_SEC".

Hope this helps

Upvotes: 4

Related Questions