473,573 Members | 3,075 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to calculate the difference between 2 timestamps then avg

348 Contributor
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 20118
pbmods
5,821 Recognized Expert Expert
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 Contributor
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 Recognized Expert Expert
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 Contributor
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 Recognized Expert Expert
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(`depar ture_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 Contributor
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(`depar ture_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 Recognized Expert Expert
Heya, Frank.

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

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

Similar topics

7
3928
by: lkrubner | last post by:
This might be an idiot question, but how do you group by timestamps by date? I mean, given a large number of timestamps, spanning many months, how do grab them and say how many are from each day? If the timestamps measure visits to a web site, how to easily say there were 45 visits on January 4th? The first idea that occurs to me is to put...
4
4445
by: Hans Gruber | last post by:
Hi all, I have been struggling with a problem all day, I have been unable to come up with a working solution. I want to write a function which takes 2 unix timestamps and calculates the difference. I want it to return the difference in years, months, days, hours, minutes and seconds (a complete summary). Keeping into account of course...
6
2318
by: Bjørn T Johansen | last post by:
I need to compute the difference of Time fields, in the format HHMM. Is it possible to do the math in the Select? Regards, BTJ -- -----------------------------------------------------------------------------------------------
5
1485
by: Robert Schuldenfrei | last post by:
Dear NG, I have not heard from anyone about a good book that deals with the concurrency issue in SQL Server using C#. I have PROMISED Nick I would not use record locking and I have used an old row / new row approach. (see next paragraph for my reply to Nick) I would like to use Timestamps and Transactions to produce an iron-clad and...
4
2157
by: Craig G | last post by:
im not too sure how i should be storing the SQL2000 timestamps basically i return a dataset which is used to populate an editable grid. this dataset contains the timestamp. how should i be storing the timestamps for each grid row? is it possible to hold them in the grid in someway? or do i need to look at other means ive only used Oracle...
3
7588
by: Eric Rechter | last post by:
Hi, What's the easiest way in php to calculate the hour and minutes (HH:MM) between 2 times? (unix timestamps) thanks in advance! Eric
13
17096
by: In a little while | last post by:
thanks
6
5662
by: moshiur | last post by:
I'm a very new programmer. I have a sorted file and want to calculate delats between the numbers. That is calculate the difference between first number in line 1 and second number in line 2 (delata1), difference number 3 in line 3 and number 2 in line 2(delta2), .... so on. Then calculate mean of these deltas, variance, standard dev and then...
7
54920
by: walt | last post by:
Hello, I have been trying to calculate the difference between two date and display the difference in hours and minutes (HH:MM). I can't get it calculate properly and I can't hours and minutes to display. I tried "results=enddate-startdate" dates have month, day, year,and time of day. After this calculation I end up with no results. Any...
1
7771
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8060
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6407
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5581
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5289
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3730
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2197
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1297
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1038
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.