We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

How to use TIMESTAMPDIFF with joined tables

Rick
Rick asked
on
Medium Priority
1,166 Views
Last Modified: 2012-05-06
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.
Comment
Watch Question

UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
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.

Author

Commented:
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.
Senior Principal Technical Support Engineer
Top Expert 2009
Commented:
Ok.. I see you are comparing only TIME values which is ok if the end time falls on same date but what if it crosses that day? you will see negative values... for better & accurate result you need to include DATE field with it... this 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;

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
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

Author

Commented:
Thank you!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.