asked on

How to use TIMESTAMPDIFF with joined tables

Hello,

How do you use TIMESTAMPDIFF when you have joined tables?

What I'm trying to do is to select the time difference between two joined tables. Some of the times are on different days (e.g. started at night, ended in the morning).

_starttime and _endtime are both on regular mysql time format (HH:MM:SS)

SELECT
`b`.`orderno`, `a`.`_starttime`, `b`.`_endtime`, TIMESTAMPDIFF(`b`.`_endtime`, `a`.`_starttime)
FROM
_tbl_b
INNER JOIN
_tbl_a AS a ON `a`.`_orderno` = `b`.`_orderno`
GROUP BY
`b`.`_orderno`

I'm using MySql 5.1.

Thank you for your help.

Umesh

If _starttime and _endtime are both on regular mysql time format (HH:MM:SS) then can you try with TIMEDIFF instead of TIMESTAMPDIFF... I see TIMESTAMPDIFF expect the parameter to be of DATE OR DATETIME format... but you are passing TIME format.

Can you pls post the table structures with sample data.. so that I can check at my end.

Rick

ASKER

What are you looking for in the table structure?

Here's some sample data:

_tbl_a

_orderno _starttime _date

AI120 22:00:58 2008-01-11
AI120b 2:09:12 2008-01-11
AI120c 8:11:03 2008-01-11

_tbl_b

_orderno _endtime _date

AI120 1:02:21 2008-01-12
AI120b 2:32:36 2008-01-11
AI120c 8:44:55 2008-01-11

Maybe I need to use something different?!?, but when I use regular TIMEDIFF I get -20:58:37 for _orderno AI120.

It should be 3:00:23 because I started the process at 22:00:58 and ended at 1:02:21 the next morning.

SELECT `b`.`_orderno`, `a`.`_starttime`, `b`.`_endtime`, TIMEDIFF(`b`.`_endtime`, (`a`.`_starttime)
FROM tbl_b AS b
INNER JOIN tbl_a AS a ON `a`.`_orderno` = `b`.`_orderno`
GROUP BY `b`.`_orderno`

Thank you.

ASKER CERTIFIED SOLUTION

Umesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Umesh

Checked at my end.. it works..


SELECT b._orderno, a._starttime, b._endtime, TIMEDIFF(CONCAT(b._date,' ',b._endtime), CONCAT(a._date,' ',a._starttime))
FROM _tbl_b AS b
INNER JOIN _tbl_a AS a ON a._orderno = b._orderno
GROUP BY b._orderno;
 
 
AI120	22:00:58	01:02:21	03:01:23
AI120b	02:09:12	02:32:36	00:23:24
AI120c	08:11:03	08:44:55	00:33:52

Open in new window

Rick

ASKER

Thank you!