[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to use TIMESTAMPDIFF with joined tables

Posted on 2009-02-16
5
Medium Priority
?
1,013 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.
0
Comment
Question by:Rick
  • 3
  • 2
5 Comments
 
LVL 26

Expert Comment

by:Umesh
ID: 23656913
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.
0
 
LVL 13

Author Comment

by:Rick
ID: 23662090
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.
0
 
LVL 26

Accepted Solution

by:
Umesh earned 2000 total points
ID: 23666554
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

0
 
LVL 26

Expert Comment

by:Umesh
ID: 23666588
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

0
 
LVL 13

Author Closing Comment

by:Rick
ID: 31547392
Thank you!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 5 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question