CODEWITHSUNDEEP
mysql
Ozi Oz
Ozi Oz

Reputation: 289

Get time difference in query

I have this query, and i need to find the difference between two time format. So i need to add a clause, which would be:

WHERE end-date - start-date should be between 01:00:00 and 05:00:00

Could you help me with turning it to a mysql statement? Thanks

 $data = mysql_query( "select r.`time`, r.`epos_id`,r.`basket_value`, r.`date`,re.`location`,i.`user_id`,i.`mobile_number`,
       i.`time`, i.`rbpos_id`, i.`date`
from retailer r
join rbpos_epos re on r.epos_id = re.epos_id
join imovo i on i.rbpos_id = re.rbpos_id
             WHERE  TIMEDIFF(i.`time`, r.`time`)<'01:05:00'
             and r.`time` < i.`time`
             and r.`date` = i.`date`;
")

Upvotes: 2

Views: 6130

Answers (4)

Ravindra Bagale
Ravindra Bagale

Reputation: 17655

TIMEDIFF() returns expr1 – expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.

The result returned by TIMEDIFF() is limited to the range allowed for TIME values. Alternatively, you can use either of the functions TIMESTAMPDIFF() and UNIX_TIMESTAMP(), both of which return integers.

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

TRY THIS

  SELECT end-date , start-date FROM `table` 
    where
  end-date - start-date between '01:00:00' and '05:00:00'

Upvotes: 0

Codesen
Codesen

Reputation: 7822

Try this

SELECT TIME_TO_SEC(TIMEDIFF('05:00:00', '01:00:00'))

Upvotes: 2

lvil
lvil

Reputation: 4326

You can use TIMEDIFF mysql function

Upvotes: 2

Related Questions