SELECT from 2tables and recieve the timediff retrieved in dateformat 1

[rogerzebra]

Hi fellas,
I really don't know how to solve this one and I hope that someone can give me some hints or point me to some good posts/tutorials. I'm trying to retrieve the time differencies between two dates.
I have two events DateR, DateE and TimeDiff in table "test".
In another table "submission" I have clients ID(name).

When I query table "test" it's register as it should the time differencies in seconds. Which I want it to and it works fine. Now I need to make a SELECT from both tables based on the ClientsName as ID from "submission" get the result from TimeDiff in table "test" and recieve the result in datetime format and not seconds.
Did you guys got that?? So, I have tried to solve this on my own, but apperently I could need some help.

SELECT DateR, DateE,
FLOOR((TO_DAYS(DateE) - TO_DAYS(DateR))/60/60/24/7/4)
AS timediff
FROM test, submission WHERE NameInsured='$NameInsured',

I know this one is a longshot but, it's what I got.
What do you guys think?
As usual all inputs are much appreciated
/r

 

[ethorn10]

You need something in the "test" table that relates to something in the "submission" table in order to get an accurate time difference. Unless the "test" table only has one record in it. Otherwise, maybe add a field to "submission" called test_id (or whatever you want to call it) that relates to the primary key (hopefully you have one) of "test." Then you can join the two tables and get your desired result. Example:

SELECT b.DateR, b.DateE, FLOOR((TO_DAYS(b.DateE)-TO_DAYS(b.DateR))/60/60/24/7/4) AS timediff FROM submission a inner join test b on a.test_id=b.test_id WHERE a.NameInsured='$NameInsured'

Hope this at least gets you started...

 

[rogerzebra]

ethorn10,
thanks a lot, that's looks nice. Yes, both tables has it's own ID's but I obviously missed that in my query.
I'll try this out.
thanks again
/r

 

[rogerzebra]

hi again,
I can't get this query to work. It returns a 0 as a result which of course it shouldn't? I still haven't really got it with join queries. So, if anyone have a suggestion to my problem or know why it does that?

$q="SELECT test.DateR, test.DateE,
	FLOOR((TO_DAYS(test.DateE) - TO_DAYS(test.DateR))/60/60/24/7/4) 
	AS timediff 
	FROM submissions INNER JOIN test 
	ON submissions.SubmissionID=test.EvaluationID
    WHERE submissions.NameInsured='test friday'";

/r

 

[ethorn10]

Actually 0 might be the true result. It may not be your EXPECTED result, but FLOOR(x) returns the largest integer not greater than x. So let's say you find the difference between DateE and DateR to be 30 days (basically a month). 30/60/60/24/7/4 = 1.24e-5 in my calculations...and the largest integer not greater than 1.24e-5 is 0. You would need 2,500,000 days between DateE and DateR to equal something larger than 0 (1). That amounts to 6,849 years...roughly.

So the query is probably working correctly...the math isn't.

 

[rogerzebra]

hehe
that's a wonderful explanation ethorn10, thanks for the lecture. I realized aswell that it wasn't correct and as soon as I took that part away it shows the result in days. So, how should I use the floor function to retrieve the result in month, days and hours then? I want to thank you for all help I couldn't do this without you.

 

[ethorn10]

Well, I think you were on the right track with the TO_DAYS calculations. You can get the number of days between the two dates with just a little fix of your existing query:

$q="SELECT test.DateR, test.DateE,
    (TO_DAYS(test.DateE) - TO_DAYS(test.DateR))
    AS timediff 
    FROM submissions INNER JOIN test 
    ON submissions.SubmissionID=test.EvaluationID
    WHERE submissions.NameInsured='test friday'";

I can't think of a way to automatically format that result into mm:dd:hh because it would technically depend on what months have passed between the two dates.

 

[rogerzebra]

ethorn, I got to work already. I see what your are saying. It seems that to retrieve the result in mm:dd:hh is to much trouble. I'm happy with just the days so, days it is.
Thanks again ethorn

*SOLVED*