CODEWITHSUNDEEP
mysql
Rak
Rak

Reputation: 139

How to display the result if the time difference is more than one hour

I want to display the result where if the time difference is more than one hour. Here is the sample query:

SELECT 
  locator_id,
  NAME,
  DATE,
  destination,
  time_leave,
  time_return,
  isOfficial 
FROM
  locator_table

and this is the result:

locator_id  name    date        destination  time_leave  time_return  isOfficial  
----------  ------  ----------  -----------  ----------  -----------  ------------
         1  ALEX    2019-07-31  FRANCE       09:40 AM    09:50 AM                0
         2  MARK    2019-07-30  GERMANY      11:00 AM    01:00 PM                1
         3  JOHN    2019-07-30  TAIWAN       11:15 AM    12:00 PM                1
         4  FOO     2019-07-31  KOREA        01:20 PM    02:30 PM                1
         5  BAR     2019-07-30  JAPAN        01:50 PM    02:00 PM                0

Now as you can see locator_id 2 and 4 are the ones with more than one hour time difference. And this should be the result:

locator_id  name    date        destination  time_leave  time_return  isOfficial  
----------  ------  ----------  -----------  ----------  -----------  ------------
         2  MARK    2019-07-30  GERMANY      11:00 AM    01:00 PM                1
         4  FOO     2019-07-31  KOREA        01:20 PM    02:30 PM                1

how to achieve this?

Upvotes: 0

Views: 676

Answers (2)

Ankur Sinha
Ankur Sinha

Reputation: 6639

You can use MySQL's TIMEDIFF function and it should be something like:

select *
from locator_table 
where 
hour(timediff(time_leave, time_return)) >= 1
and 
minute(timediff(time_leave, time_return)) >= 1

Fiddle Demo

Upvotes: 1

Christiaan Nieuwlaat
Christiaan Nieuwlaat

Reputation: 1359

If the time_leave and time_return columns are of the TIME or DATETIME type, you could use the MySQL TIMEDIFF function to calculate the difference, use the HOUR function to extract the hours part from the difference and select rows accordingly.

This statement would do the trick:

SELECT 
  locator_id,
  NAME,
  DATE,
  destination,
  time_leave,
  time_return,
  isOfficial 
FROM
  locator_table 
WHERE 
  HOUR(TIMEDIFF(time_return, time_leave)) > 1

Please be aware that this will only work with DATETIME or TIME typed values. If the values are VARCHAR or CHAR typed, you'll need to do some conversion to TIME or DATETIME types on beforehand.

Upvotes: 0

Related Questions