472,145 Members | 1,530 Online

# How to calculate the difference between 2 timestamps then avg

348 100+
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.

Expand|Select|Wrap|Line Numbers
1. SELECT AVG(TIMEDIFF(departure_Time, arrival_Time)) as average
2. FROM t1
3. WHERE
4. employee_Number= '3'
I have two stamps:

Expand|Select|Wrap|Line Numbers
1. departure_Time                arrival_Time
2. 2007-09-23 13:15:29        2007-09-23 13:42:45
3. 2007-09-23 17:26:41        2007-09-23 17:27:41
4.
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
Sep 23 '07 #1
7 19952
pbmods
5,821 Expert 4TB
Heya, Frank.

The 1408 that you're seeing is 1408 seconds. The average difference is actually 23 minutes and 28 seconds.
Sep 23 '07 #2
fjm
348 100+
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
Sep 23 '07 #3
pbmods
5,821 Expert 4TB
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.
Sep 23 '07 #4
fjm
348 100+
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.
Sep 23 '07 #5
pbmods
5,821 Expert 4TB
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`):

Expand|Select|Wrap|Line Numbers
1. INSERT
2.     INTO
3.         `t1`
4.         (
5.             .
6.             .
7.             .
8.             `departure_Time`,
9.             `arrival_Time`,
10.             `time_diff`
11.         )
12.     VALUES
13.         (
14.             .
15.             .
16.             .
17.             '{\$departure_Time}',
18.             '{\$arrival_Time}',
19.             TIMEDIFF('{\$departure_Time}', '{\$arrival_Time}')
20.         )
21.
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.
Sep 23 '07 #6
fjm
348 100+
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`):

Expand|Select|Wrap|Line Numbers
1. INSERT
2. INTO
3. `t1`
4. (
5. .
6. .
7. .
8. `departure_Time`,
9. `arrival_Time`,
10. `time_diff`
11. )
12. VALUES
13. (
14. .
15. .
16. .
17. '{\$departure_Time}',
18. '{\$arrival_Time}',
19. TIMEDIFF('{\$departure_Time}', '{\$arrival_Time}')
20. )
21.
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
Sep 23 '07 #7
pbmods
5,821 Expert 4TB
Heya, Frank.

Good luck with your project, and if you ever need anything, post back anytime :)
Sep 23 '07 #8