self join

[Andrew]

I want to find the difference in times between the date/times of two
tasks in the same table.

selfJoin table
task_ID task date_time
1 measure board 2004-11-28 15:53:55
2 cut board 2004-11-28 16:08:03

Here is the best SQL statement I can come up with to find how much
time has passed between those two events:

$sql = "SELECT DIFF(first.date_time, second.date_time) FROM selfJoin
AS first, selfJoin AS second";

What is the correct way to do this?

(Never mind that there will in fact be many tasks in each table. And
I will also want to know the total time difference between the first
task and the last task.)

Thanks,
-Andrew K

[Bill Karwin]

Andrew wrote:

I want to find the difference in times between the date/times of two
tasks in the same table.

selfJoin table
task_ID task date_time
1 measure board 2004-11-28 15:53:55
2 cut board 2004-11-28 16:08:03

Here is the best SQL statement I can come up with to find how much
time has passed between those two events:

$sql = "SELECT DIFF(first.date_time, second.date_time) FROM selfJoin
AS first, selfJoin AS second";

DIFF() is not a function in MySQL as far as I know.
Read the docs on date/time functions:
http://dev.mysql.com/doc/mysql/en/Da...functions.html

I would suggest this:

SELECT UNIX_TIMESTAMP(second.date_time)
- UNIX_TIMESTAMP(first.date_time)
FROM selfJoin AS first, selfJoin AS second
WHERE first.task_ID <> second.task_ID

This returns the difference in seconds between those two date/times,
assuming both are between 1970 and 2037. :-)

I'm assuming you left out any join condition because you want all rows
to be compared to all other rows. It's often handy to add a condition
as I have above, so you don't get each row compared to itself.

Regards,
Bill K.

[Azeus]

"Andrew" <ak****@web4000.com> wrote in message
news:42**************************@posting.google.c om...

I want to find the difference in times between the date/times of two
tasks in the same table.

selfJoin table
task_ID task date_time
1 measure board 2004-11-28 15:53:55
2 cut board 2004-11-28 16:08:03

Here is the best SQL statement I can come up with to find how much
time has passed between those two events:

$sql = "SELECT DIFF(first.date_time, second.date_time) FROM selfJoin
AS first, selfJoin AS second";

What is the correct way to do this?

MySql offers several date/time functions

DATEDIFF and TIMEDIFF.

DATEDIFF Only returns the difference in days, while TIMEDIFF returns the
difference in the following format:

hours:minutes:seconds:milliseconds

But only from MySql 4.1.1

Check http://dev.mysql.com/doc/mysql/en/Da...functions.html