MYSQL - SQL Work out the average time difference between total rows

I've searched around SO and can't seem to find a question with an answer that works fine for me. I have a table with almost 2 million rows in, and each row has a MySQL Date formatted field.

I'd like to work out (in seconds) how often a row was inserted, so work out the average difference between the dates of all the rows with a SQL query.

Any ideas? Thanks in advanced :)

-- EDIT -- Heres what my table looks like

id, name, date (datetime), age, gender

This question and answers originated from www.stackoverflow.com
Question by (5/31/2011 5:36:12 PM)

Answer

If you want to know how often (on average) a row was inserted, I don't think you need to calculate all the differences. You only need to sum up the differences between adjacent rows (adjacent based on the timestamp) and divide the result by the number of the summands.

The formula

((T1-T0) + (T2-T1) + … + (TN-TN-1)) / N

can obviously be simplified to merely

(TN-T0) / N

So, the query would be something like this:

SELECT TIMESTAMPDIFF(SECOND, MIN(date), MAX(date)) / (COUNT(*) - 1)
FROM atable

Make sure the number of rows is more than 1, or you'll get the Division By Zero error. Still, if you like, you can prevent the error with a simple trick:

SELECT
  IFNULL(TIMESTAMPDIFF(SECOND, MIN(date), MAX(date)) / NULLIF(COUNT(*) - 1, 0), 0)
FROM atable

Now you can safely run the query against a table with a single row.

Answer by
Find More Answers
Related Topics  mysql  sql  time  difference  average
Related Questions

  • SQL Query to find average of difference between dates

    I have a column that has data like Date 13/8/2011 2/9/2011 10/9/2011 20/9/2011 I need to write a SQL query/procedure that will help me get the average of the differences between the dates. Fo…

  • How to find the average time difference between rows in a table?

    I have a mysql database that stores some timestamps. Let's assume that all there is in the table is the ID and the timestamp. The timestamps might be duplicated. I want to find the average time d…

  • Mysql Average on time column?

    SELECT avg( duration ) as average FROM `login`; The datatype for duration is "time", thus my value is like: 00:00:14, 00:20:23 etc I execute the query it gives me: 2725.78947368421 What is…

  • Calculate Time Difference Between Two Rows SQL Server

    I have a table that contains the following: DataDate Value 2010-03-01 08:31:32.000 100 2010-03-01 08:31:40.000 110 2010-03-01 08:31:42.000 95 2010-03-01 08:31:45.000 101 . . . …

  • ACCESS/SQL: Calculating the difference between rows (not dates)

    I looked through the questions here but didn't find one that suited my case. I'm trying to write a query that will output the difference between rows Here is a table: ITEM CYCLES ------…

  • SQL To find difference between multiple rows

    I have a table containing multiple records for different transactions i.e. ID Date REF 1 01/09/2008 A 1 11/09/2008 A 1 01/10/2008 A 2 01/09/2008 A 2 01/10/2008 A 2 01/11/2008 B 2 …

  • Average Time to Reply to Message

    Is it possible to calculate the average time to reply to a message just with the following columns: id | ref | client | admin | date | message id is the unique message number ref is the m…

  • Time Difference between query result rows: How To?

    The output below is part of the result of a query on a table 'Reviews'. CustomerName ReviewDT Doe,John 2011-06-20 10:13:24 Doe,John 2011-06-20 10:54:45 Doe,John 2011-06-20 11:36:34 Doe,…

  • What's the difference between PHP time and SQL time?

    Why is the timestamp generated by the PHP time() function so different from SQL datetime? If I do a date('Y-m-d', time()); in PHP, it gives me the time now, as it should. If I just take the time(…

  • MYSQL - how to get a time difference?

    In MySQL I am trying to select rows from a table that have a lock_dt older than 10 hours. How can I write this sql statement properly? select phone from table where ((now() - lock_dt) < 10 hou…