CODEWITHSUNDEEP
mysqlsqldatetimetime
Ted
Ted

Reputation: 27

MYSQL ignore negitive time value in TIMEDIFF

I am working on a time system that require manual input of the coming and going times. (not my system) I am building a dashboard for this system that will show average time on site and more. Because it requires a manually entered coming and going time, mistakes can happen. If someone checks in at 18:00hours but forgets to clock out, the system automatically leaves the clock out time at 0:00:00 hours.

When calculating my averages, if the above occurs, then it calculates the average time spent on site and adds in a -18:00 hours. This obviously breaks the whole calculation. Is there a way to have the query ignore any negatives to avoid this?

SELECT id, TIMEDIFF(`booking_time_out`, `booking_time`) AS 'Time_Spent'
FROM `table_name`

Upvotes: 0

Views: 110

Answers (2)

GMB
GMB

Reputation: 222582

You seem to want a case expression:

select id,
    case when timediff(`booking_time_out`, `booking_time`) < 0
        then 0
        else timediff(`booking_time_out`, `booking_time`) 
    end as time_spent
from tablename

Side note: do not surround identifiers with single quotes (as in as 'Time_spent'). In standard SQL, single quotes stand for literal strings. On the other hand, you usually do not need to quote an identifier (unless its name is really badly chosen) - and if you need to, the quoting character of MySQL is backticks.

Upvotes: 1

danblack
danblack

Reputation: 14736

The negative result criteria is a result of the 0:00:00 booking out time so append the exclusion of that row in the where criteria like:

where booking_time_out != '0:00:00'

Upvotes: 1

Related Questions