How to Make an Average of Dates in MySQL

How can I make an average of dates in MySQL?

This seems a bit hackish, but will work for dates beteen ~ 1970 and 2030 (on 32 bit arch). You are essentially converting the datetime values to integer, averaging them, and converting the average back to a datetime value.

SELECT
    from_unixtime(
        avg(
            unix_timestamp(date_one)-unix_timestamp(date_two)
        )
    )
FROM
    some_table
WHERE
    some-restriction-applies

There is likely a better solution out there, but this will get you by in a pinch.

How do I calculate an average date in SQL?

Try:

SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(created))) FROM table

Calculating average time between dates in SQL

You can use window functions. I am thinking row_number() to enumerate the projects of each user ordered by creation date, and lag() to get the date when the previous project was created:

select rn, avg(datediff(created_at, lag_created_at)) avg_diff_days
from (
    select t.*,
        row_number() over(partition by user_id order by created_at) rn,
        lag(created_at, 1, created_at) over(partition by user_id order by created_at) lag_created_at
    from mytable t
) t
group by rn

This gives you the average difference in days, which is somehow more accurates that months. If you really want months, then use timestampdiff(month, lag_created_at, created_at) instead of datediff() - but be aware that the function returns an integer value, hence there is a loss of precision.

How to get AVG from DATE (not datetime, timestamp) and return DATE (not datetime, timestamp)

Convert the date to unixtime, take the average and convert back to datetime:

select cast(from_unixtime(avg(unix_timestamp(dt))) as date)
from data;

Unixtime is simply seconds elapsed from 1970-01-01, so it is an integer where you can calculate the average from.

MySql average date difference

The average is just the largest value minus the smallest divided by one less than the number of values. So:

select datediff(max(date), min(date)) / nullif(count(*) - 1, 0)
from t;

You can easily see this if you look at the numbers:

 1
 5       . . . 4
 10      . . . 5
 19      . . . 9

The average difference is (4 + 5 + 9) / 3 = 6.

It is not a coincidence that (19 - 1) / 3 = 6 as well. It is a mathematical certainty.

You can easily see this. The average difference is:

( (5 - 1) + (10 - 5) + (19 - 10) ) / 3

You can rearrange this:

( -1 + (5 - 5) + (10 - 10) + 19 ) / 3

which is:

( 19 - 1 ) / 3

Calculate average, minimum, maximum interval between date

If your mysql version didn't support lag or lead function.

You can try to make a column use a subquery to get next DateTime. then use DATEDIFF to get the date gap in a subquery.

Query 1:

SELECT avg(diffDt),min(diffDt),MAX(diffDt)
FROM (
  SELECT  DATEDIFF((SELECT transaction_date 
                            FROM  T tt
                            WHERE tt.transaction_date > t1.transaction_date
                            ORDER BY tt.transaction_date 
                            LIMIT 1
                           ),transaction_date) diffDt
  FROM T t1
) t1

Results:

| avg(diffDt) | min(diffDt) | MAX(diffDt) |
|-------------|-------------|-------------|
|           2 |           1 |           3 |

if your mysql version higher than 8.0 you can try to use LEAD window function instead of subquery.

---

Query #1

SELECT avg(diffDt),min(diffDt),MAX(diffDt)
FROM (
  SELECT  DATEDIFF(LEAD(transaction_date) OVER(ORDER BY transaction_date),transaction_date) diffDt
  FROM T t1
) t1;

| avg(diffDt) | min(diffDt) | MAX(diffDt) |
| ----------- | ----------- | ----------- |
| 2           | 1           | 3           |

---

View on DB Fiddle

Make the average of values for time intervals mysql

One way could be to extract date and hour part from the timestamp and group by the resultant.

select DATE_ADD(date(fecha), INTERVAL EXTRACT(HOUR FROM fecha) HOUR) as FECHA_DATE_HOUR, 
       avg(Valor_Dispositivo) as Valor_Dispositivo
from Telegramas
group by date(fecha), EXTRACT(HOUR FROM fecha);

Result:

+---------------------+-------------------+
|   FECHA_DATE_HOUR   | Valor_Dispositivo |
+---------------------+-------------------+
| 14.12.2017 11:00:00 | 4.3333            |
| 14.12.2017 12:00:00 | 5.0000            |
+---------------------+-------------------+

DEMO

How to average a value in a time window for multiple days, in Mysql?

You do not need to use Subquery, to get the data and then calculate the average in the Outer query. It can be done without the subquery itself.

In order to get the average value for different dates, you can remove the WHERE condition on date, to get the data for all the date(s). You can also change the WHERE condition on date to include a range instead.

You also seem to want results for different id values. We can get rid of WHERE condition on id as well.

Eventually, you can do a GROUP BY on the id and date, to get individual rows idwise, and then datewise.

SELECT 
  id, 
  DATE(dt)
  AVG(val) 
FROM test
WHERE 
  TIME(dt) BETWEEN '00:00' AND '02:00'
GROUP BY id, DATE(dt)

find average interval dates from the date colum in mysql server

nb: It is easier to add another answer than to edit the former.

1. You need to use JOINED "derived tables" instead of "correlated subqueries". You will find this far more efficient too. Here you need to average some values so the derived table involves a group by.

2. To use the technique where a previous value is carried over to the next row, you must cross join some variables, don't comment this out.

3. The order by clause is vital to this technique. Here you must use a combined order involving dam_id as well as birth_date otherwise you would get a rubbish result.

Hopefully these queries will identify the logic for you. The first helps display the detailed logic of each row. The second displays the "derived table" before it is joined, and the third displays the effect of joining the derived table to the source (detail) table.

Query 1:

SELECT
      IF((t2.dam_id=@prev_dam), datediff(t2.birth_date,@prev_value), NULL) difference
    , @prev_dam
    , @prev_value
    , t2.dam_id
    , t2.birth_date
    , @prev_dam := t2.dam_id
    , @prev_value := t2.birth_date
FROM cattle_info_tbl t2
CROSS JOIN (SELECT @prev_dam:=null x, @prev_value:=str_to_date(NULL,'%Y-%M-%d') y) y
order by t2.dam_id, t2.birth_date

Results:

| difference | @prev_dam | @prev_value | dam_id | birth_date | @prev_dam := t2.dam_id | @prev_value := t2.birth_date |
|------------|-----------|-------------|--------|------------|------------------------|------------------------------|
|     (null) |    (null) |      (null) |  S6040 | 2008-04-30 |                  S6040 |                   2008-04-30 |
|        351 |     S6040 |  2008-04-30 |  S6040 | 2009-04-16 |                  S6040 |                   2009-04-16 |
|        336 |     S6040 |  2009-04-16 |  S6040 | 2010-03-18 |                  S6040 |                   2010-03-18 |
|     (null) |     S6040 |  2010-03-18 |  S6093 | 2008-04-04 |                  S6093 |                   2008-04-04 |
|        376 |     S6093 |  2008-04-04 |  S6093 | 2009-04-15 |                  S6093 |                   2009-04-15 |
|        353 |     S6093 |  2009-04-15 |  S6093 | 2010-04-03 |                  S6093 |                   2010-04-03 |
|        344 |     S6093 |  2010-04-03 |  S6093 | 2011-03-13 |                  S6093 |                   2011-03-13 |
|        444 |     S6093 |  2011-03-13 |  S6093 | 2012-05-30 |                  S6093 |                   2012-05-30 |
|        351 |     S6093 |  2012-05-30 |  S6093 | 2013-05-16 |                  S6093 |                   2013-05-16 |
|        362 |     S6093 |  2013-05-16 |  S6093 | 2014-05-13 |                  S6093 |                   2014-05-13 |
|     (null) |     S6093 |  2014-05-13 |  S6094 | 2008-03-29 |                  S6094 |                   2008-03-29 |
|        371 |     S6094 |  2008-03-29 |  S6094 | 2009-04-04 |                  S6094 |                   2009-04-04 |
|        409 |     S6094 |  2009-04-04 |  S6094 | 2010-05-18 |                  S6094 |                   2010-05-18 |
|        300 |     S6094 |  2010-05-18 |  S6094 | 2011-03-14 |                  S6094 |                   2011-03-14 |
|       1185 |     S6094 |  2011-03-14 |  S6094 | 2014-06-11 |                  S6094 |                   2014-06-11 |

Query 2:

SELECT dam_id, AVG(difference) age
FROM (
      SELECT
            IF((t2.dam_id=@prev_dam), datediff(t2.birth_date,@prev_value), NULL) difference
          , t2.dam_id
          , @prev_dam := t2.dam_id
          , @prev_value := t2.birth_date
      FROM cattle_info_tbl t2
      CROSS JOIN (SELECT @prev_dam:=null x, @prev_value:=str_to_date(NULL,'%Y-%M-%d') y) y
      ORDER BY t2.dam_id, t2.birth_date
     ) b
GROUP BY dam_id

Results:

| dam_id |      age |
|--------|----------|
|  S6040 |    343.5 |
|  S6093 | 371.6667 |
|  S6094 |   566.25 |

Query 3:

SELECT
       t1.dam_id as cow_id
     , av.age
FROM cattle_info_tbl t1
LEFT JOIN (
          SELECT dam_id, AVG(difference) age
          FROM (
                SELECT
                      IF((t2.dam_id=@prev_dam), datediff(t2.birth_date,@prev_value), NULL) difference
                    , t2.dam_id
                    , @prev_dam := t2.dam_id
                    , @prev_value := t2.birth_date
                FROM cattle_info_tbl t2
                CROSS JOIN (SELECT @prev_dam:=null x, @prev_value:=str_to_date(NULL,'%Y-%M-%d') y) y
                ORDER BY t2.dam_id, t2.birth_date
               ) b
          GROUP BY dam_id
          ) av ON t1.dam_id = av.dam_id
WHERE t1.herd_id = 'H38' AND t1.dam_id<>''

Results:

| cow_id |      age |
|--------|----------|
|  S6093 | 371.6667 |
|  S6093 | 371.6667 |
|  S6094 |   566.25 |
|  S6094 |   566.25 |
|  S6093 | 371.6667 |
|  S6040 |    343.5 |
|  S6094 |   566.25 |
|  S6093 | 371.6667 |
|  S6040 |    343.5 |
|  S6040 |    343.5 |
|  S6093 | 371.6667 |
|  S6094 |   566.25 |
|  S6093 | 371.6667 |
|  S6094 |   566.25 |
|  S6093 | 371.6667 |

NOTE: I think you complicate everything by confusing dam_id with cow_id. This does not appear to be correct. My guess is that animal_id is more likely to be the correct column to relabel as cow_id.

Getting total average between dates

You can find average of total for each user within 10 days date range from intial sales date like this:

select avg(sale_cost)
from (
    select sum(t.sale_cost) sale_cost
    from your_table t
    join (
        select user_id, min(sale_date) start_date, date_add(min(sale_date), interval 10 day) end_date
        from your_table
        group by user_id
        ) t2 on t.user_id = t2.user_id
        and t.sale_date between t2.start_date and t2.end_date
    group by t.user_id
    ) t;

It finds the first sale_date and date 10 days after this for each user. Then joins it with the table to get total for each user within that range and then finally average of the above calculated totals.

Demo

If you want to find the average between overall first sale_date (not individual) and 10 days from it, use:

select avg(sale_cost)
from (
    select sum(t.sale_cost) sale_cost
    from your_table t
    join (
        select min(sale_date) start_date, date_add(min(sale_date), interval 10 day) end_date
        from your_table
        ) t2 on t.sale_date between t2.start_date and t2.end_date
    group by t.user_id
    ) t;

Demo

---

Related Topics