1

I have a table, that has a unix timestamp field for start and end of a process.

I have seen a number of suggestions that appear to calculate averages unsing Datediff, but as I am using unix timestamps, this isn't necessary, is it?

I can calculate the time difference for each entry (end-start) but how can I calculate an average of all of these differences across the entire data set?

I tried;

SELECT AVG(endtime-starttime) AS length FROM survey WHERE complete = '1'

But the value it returns is negative, and so I am thinking that I have got this all wrong.

Any advice?

Sean in NZ
  • 11
  • 2
  • Possible duplicate of [Average difference between two dates, grouped by a third field?](https://stackoverflow.com/questions/14895782/average-difference-between-two-dates-grouped-by-a-third-field) – John Ellmore Aug 21 '18 at 05:10
  • 1
    Is there always an `endtime`? – juergen d Aug 21 '18 at 05:11
  • There is an endtime if the survey is complete (complete = '1') – Sean in NZ Aug 21 '18 at 05:19
  • I've read the article that John suggested, but I can't get my head around the difference in application between datetime and unix timestamps – Sean in NZ Aug 21 '18 at 05:20
  • 4
    The SQL looks right, so I think you probably have some rows with invalid or unexpected data. Try: `select * from survey where complete = '1' and (endtime < starttime or endtime is null or starttime is null)` – We Are All Monica Aug 21 '18 at 05:21
  • Thank you. I just hunted through the table, and found a single entry from a testing phase where there was no endtime. I now have a much more reasonable calculation. Thank juergen d and jnylen. – Sean in NZ Aug 21 '18 at 05:29

0 Answers0