How to calculate the difference between 2 timestamps then avg

[fjm]

Hi all,

I have a problem getting a correct result with my sql code and figured I would see if anybody could help.

I have two fields in a table that have timestamps. I want to find the difference in time between the two stamps and then calculate the average. Here is the code that I have come up with so far.

SELECT AVG(TIMEDIFF(departure_Time, arrival_Time)) as average
FROM t1
WHERE
employee_Number= '3'

I have two stamps:

departure_Time                arrival_Time
2007-09-23 13:15:29        2007-09-23 13:42:45
2007-09-23 17:26:41        2007-09-23 17:27:41
 

Based on those timestamps and running the code will return '1408' which I believe is 14 minutes and 8 seconds. I believe that the calculations may be correct but I would either like to have a decimal point or just round out the number to 14. Again, this is the average between the two stamps.

Also, if someone has a better way of doing this, I am open to suggestions.

Thanks guys!

Frank

[pbmods]

Heya, Frank.

The 1408 that you're seeing is 1408 seconds. The average difference is actually 23 minutes and 28 seconds.

[fjm]

Heya, Frank.

The 1408 that you're seeing is 1408 seconds. The average difference is actually 23 minutes and 28 seconds.

Hey Pbmods! I'm glad to see that you are over on this side of the scripts as well. :)

Thanks for the help. Is there a way to convert the seconds to minutes by chance?

Thanks,

Frank

[pbmods]

Heya, Frank.

Ermmmmmmmmm... divide by 60?

What you probably meant was how to get the minutes and then the remaining seconds. Fortunately, PHP has a modulus operator.

[fjm]

Heya, Frank.

Ermmmmmmmmm... divide by 60?

What you probably meant was how to get the minutes and then the remaining seconds. Fortunately, PHP has a modulus operator.

LOL.. I'm embarrased. :)

Would you recommend that I do the diff and averages in php rather in mysql?

Thanks Pbmods.

[pbmods]

Heya, Frank.

I'd recommend calculating the averages in MySQL simply because then you are fetching fewer rows from the database. The calculations probably take about the same amount of time on either side, but to run the calculations on the PHP side, you have to first fetch every row for that employee.

Now, if you REALLY wanted to speed things up, you could add a `time_diff` column to your table that stores the result of TIMEDIFF(`departure_Time`, `arrival_Time`):

INSERT
    INTO
        `t1`
        (
            .
            .
            .
            `departure_Time`,
            `arrival_Time`,
            `time_diff`
        )
    VALUES
        (
            .
            .
            .
            '{$departure_Time}',
            '{$arrival_Time}',
            TIMEDIFF('{$departure_Time}', '{$arrival_Time}')
        )
 

The `time_diff` column (or whatever you decide to call it) would naturally be an int.
Then, as long as the `time_diff` column is indexed, it becomes VERY fast to merely average this column.

[fjm]

Heya, Frank.

I'd recommend calculating the averages in MySQL simply because then you are fetching fewer rows from the database. The calculations probably take about the same amount of time on either side, but to run the calculations on the PHP side, you have to first fetch every row for that employee.

Now, if you REALLY wanted to speed things up, you could add a `time_diff` column to your table that stores the result of TIMEDIFF(`departure_Time`, `arrival_Time`):

INSERT
INTO
`t1`
(
.
.
.
`departure_Time`,
`arrival_Time`,
`time_diff`
)
VALUES
(
.
.
.
'{$departure_Time}',
'{$arrival_Time}',
TIMEDIFF('{$departure_Time}', '{$arrival_Time}')
)
 

The `time_diff` column (or whatever you decide to call it) would naturally be an int.
Then, as long as the `time_diff` column is indexed, it becomes VERY fast to merely average this column.

Your good, Pbmods.. That's a great idea! Thank you so much!!

Frank

[pbmods]

Heya, Frank.

Good luck with your project, and if you ever need anything, post back anytime :)