By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,492 Members | 1,199 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,492 IT Pros & Developers. It's quick & easy.

How to calculate the difference between 2 timestamps then avg

100+
P: 348
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
Share this Question
Share on Google+
7 Replies


pbmods
Expert 5K+
P: 5,821
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

100+
P: 348
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
Sep 23 '07 #3

pbmods
Expert 5K+
P: 5,821
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

100+
P: 348
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.
Sep 23 '07 #5

pbmods
Expert 5K+
P: 5,821
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

100+
P: 348
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`):

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
Expert 5K+
P: 5,821
Heya, Frank.

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

Post your reply

Sign in to post your reply or Sign up for a free account.