compare date/time values in the same table

I have a table that is a project. Each record is a task in the
project. One field in each record is a date/time stamp for when that
task was completed. I need to be able to: 1) compute how much time has
passed between each date/time stamp and 2) compute how much time has
passed between the first and last date/time stamp (total project time).
This involves a self join, and I have nearly gotten it to work, but I
need a little more help.

Here is an example of the "selfJoin" table:

task_ID task date_time
1 go to grocery store 2004-12-01 15:53:55
2 cook food 2004-12-03 12:00:00
3 serve food 2004-12-03 18:00:00

Here is the best SQL statement I can come up with:

$sql = "SELECT first.task_ID, first.task, first.date_time,
UNIX_TIMESTAMP(second.date_time) - UNIX_TIMESTAMP(first.date_time)
FROM selfJoin AS first, selfJoin AS second
WHERE first.task_ID < second.task_ID";

Here is what I want the result to look like:

task_ID task date_time total time
1 go to grocery store 2004-12-01 15:53:55
2 cook food 2004-12-03 17:00:00 197065 seconds
3 serve food 2004-12-03 18:00:00 3600 seconds

Total project time: 201965 seconds

Except my SQL statement doesn't produce this. (I have PHP functions to
reformat the times to a nice format.)

What SQL statement determines the difference between a task's date_time
value and the one before it? Ie, how long each task took?
Thanks,
-Andrew K.

re: compare date/time values in the same table

akoper@web4000.com wrote:
[color=blue]
> task_ID task date_time total time
> 1 go to grocery store 2004-12-01 15:53:55
> 2 cook food 2004-12-03 17:00:00 197065 seconds
> 3 serve food 2004-12-03 18:00:00 3600 seconds[/color]
[color=blue]
> What SQL statement determines the difference between a task's date_time
> value and the one before it? Ie, how long each task took?[/color]

Select all required rows with columns and select datetimes using
UNIX_TIMESTAMP() function in MySQL. Then calculate the differences in
PHP. It might be possible to do that in MySQL, but it is far more
simpler to do that on PHP and that is how we do that at work for example.

re: compare date/time values in the same table

<akoper@web4000.com> wrote in message
news:1103840465.803864.152550@f14g2000cwb.googlegr oups.com...[color=blue]
> I have a table that is a project. Each record is a task in the
> project. One field in each record is a date/time stamp for when that
> task was completed. I need to be able to: 1) compute how much time has
> passed between each date/time stamp and 2) compute how much time has
> passed between the first and last date/time stamp (total project time).
> This involves a self join, and I have nearly gotten it to work, but I
> need a little more help.
>
> Here is an example of the "selfJoin" table:
>
> task_ID task date_time
> 1 go to grocery store 2004-12-01 15:53:55
> 2 cook food 2004-12-03 12:00:00
> 3 serve food 2004-12-03 18:00:00
>
> Here is the best SQL statement I can come up with:
>
> $sql = "SELECT first.task_ID, first.task, first.date_time,
> UNIX_TIMESTAMP(second.date_time) - UNIX_TIMESTAMP(first.date_time)
> FROM selfJoin AS first, selfJoin AS second
> WHERE first.task_ID < second.task_ID";
>
> Here is what I want the result to look like:
>
> task_ID task date_time total time
> 1 go to grocery store 2004-12-01 15:53:55
> 2 cook food 2004-12-03 17:00:00 197065 seconds
> 3 serve food 2004-12-03 18:00:00 3600 seconds
>
> Total project time: 201965 seconds
>
> Except my SQL statement doesn't produce this. (I have PHP functions to
> reformat the times to a nice format.)
>
> What SQL statement determines the difference between a task's date_time
> value and the one before it? Ie, how long each task took?
> Thanks,
> -Andrew K.[/color]

This is a tricky query. First, it's easy to get the difference of row n and
row n+1. But's it's hard to display the difference in row n. I might have a
solution, but could you post you CREATE TABLE statements and some sample
INSERTS? And also, what time interval are you looking for between the tasks
? i.e minutes, hours, days, weeks, etc.

Best Regards,
Rich

re: compare date/time values in the same table

I was on Christmas vacation and am just getting back to this. I am not
sure why you need a create table or insert statement - I think the
example table shows you everything you need to know. Table:

task_ID task date_time
1 plan meal 2004-12-01 9:53:00
2 go to grocery store 2004-12-01 15:53:55
3 cook food 2004-12-03 12:00:00
4 serve food 2004-12-03 18:00:00

What SQL statement shows me how many seconds have passed between tasks
2 & 1, 3 & 2 and 4 & 3 for n number of tasks?

What SQL statement shows me how many seconds have passed between tasks
1 and the final task?

Thanks,
-AK

re: compare date/time values in the same table

akoper@web4000.com wrote:
[color=blue]
> I was on Christmas vacation and am just getting back to this. I am not
> sure why you need a create table or insert statement - I think the
> example table shows you everything you need to know. Table:
>
> task_ID task date_time
> 1 plan meal 2004-12-01 9:53:00
> 2 go to grocery store 2004-12-01 15:53:55
> 3 cook food 2004-12-03 12:00:00
> 4 serve food 2004-12-03 18:00:00
>
> What SQL statement shows me how many seconds have passed between tasks
> 2 & 1, 3 & 2 and 4 & 3 for n number of tasks?[/color]

SELECT T.task_ID, T.task, T.date_time,
COALESCE(PrevT.task_ID),
UNIX_TIMESTAMP(T.date_time) - UNIX_TIMESTAMP(PrevT.date_time),
NULL) AS TaskDuration
FROM selfJoin AS T LEFT OUTER JOIN selfJoin AS PrevT
ON PrevT.task_ID = T.task_ID - 1
[color=blue]
> What SQL statement shows me how many seconds have passed between tasks
> 1 and the final task?[/color]

SELECT UNIX_TIMESTAMP(LaterT.date_time) -
UNIX_TIMESTAMP(EarlierT.date_time) AS TotalProjectTime
FROM selfJoin AS EarlierT INNER JOIN LaterT
WHERE EarlierT.task_ID = (SELECT MIN(task_ID) FROM selfJoin)
AND LaterT.task_ID = (SELECT MAX(task_ID) FROM selfJoin)

Regards,
Bill K.

re: compare date/time values in the same table

<akoper@web4000.com> wrote in message
news:1104434655.205087.241830@c13g2000cwb.googlegr oups.com...[color=blue]
> I was on Christmas vacation and am just getting back to this. I am not
> sure why you need a create table or insert statement - I think the
> example table shows you everything you need to know. Table:
>
> task_ID task date_time
> 1 plan meal 2004-12-01 9:53:00
> 2 go to grocery store 2004-12-01 15:53:55
> 3 cook food 2004-12-03 12:00:00
> 4 serve food 2004-12-03 18:00:00
>
> What SQL statement shows me how many seconds have passed between tasks
> 2 & 1, 3 & 2 and 4 & 3 for n number of tasks?
>
> What SQL statement shows me how many seconds have passed between tasks
> 1 and the final task?
>
> Thanks,
> -AK
>[/color]

In general, to get intra-row differences :

create table diffs(
col1 int);
insert into diffs values(3),(4),(9),(26);

select d1.col1,
(select min(d2. col1) - d1.col1 from diffs as d2 where d2.col1 >d1.col1)
from diffs as d1

To do the same thing with datetimes:

create table TimeDiffs(
col1 datetime);

/* execute the following, waiting a few seconds between executions */
insert into TimeDiffs values(now())

select d1.col1,
(select UNIX_TIMESTAMP(min(d2. col1))- UNIX_TIMESTAMP(d1.col1) from
TimeDiffs as d2 where d2.col1 >d1.col1)
from TimeDiffs as d1

To get the difference between the last task and the first task (assuming
each task starts later than the previous on:

select UNIX_TIMESTAMP(max(col1)) - UNIX_TIMESTAMP(min(col1)) from TimeDiffs

The only problem I have with your spec is that all you're getting is the
time between the start time of a task and the start time of the next task
(if date_time is the start date. It could be the end date). You're not
getting the duration of each task unless each task starts immediately after
the previous task (not likely).

Also, if you store date_time as a timestamp, then you'll lose it if you
update task.

I looked at Bill's response but didn't quite understand it, so maybe I don't
really understand what you're looking for.

Hope this helped--

Regards,
Rich