CODEWITHSUNDEEP
mysqlsqlpostgresqlaverage
TheDataGuy
TheDataGuy

Reputation: 3108

SQL - Find average interval between rows in the timestamp column

In the PostgreSQL and MySQL tables, I have a timestamp column that indicates the data import timestamp. It's based on some scheduled interval. I need to find the avg(or approximate) time interval for the loads.

2021-04-18 06:10:26 | Loaded
2021-04-19 00:10:32 | Loaded
2021-04-19 01:10:26 | Loaded
2021-04-19 02:40:24 | Loaded
2021-04-19 04:10:20 | Loaded
2021-04-18 11:10:24 | Loaded
2021-04-18 20:40:28 | Loaded
2021-04-18 00:10:25 | Loaded
2021-04-18 01:10:22 | Loaded
2021-04-18 01:40:22 | Loaded
2021-04-18 13:40:24 | Loaded
2021-04-18 14:10:21 | Loaded

Here, approximately every 30mins, the data gets loaded.

I want to write SQL to get this. (in PostgreSQL and MySQL)

I tried this,

select avg(starttime) but its showing,

Invalid operation: function avg(timestamp without time zone) does not exist;

Upvotes: 1

Views: 1138

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The the simplest method is:

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

Neither subqueries nor window fucntions are needed for this calculation.

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

For MySQL8.0 and PostgreSQL:

with cte as
(
   select timestamp_column- lead(timestamp_column) over (order by timestamp_column) time_diff
   from tablename
) 
select avg(time_diff) from cte

For older version of MySQL

select avg(time_diff) from
       (
       select timestamp_column- (select min(timestamp_column)from tablename tn
       where  tn.timestamp_column>t.timestamp_column) time_diff
       from tablename t
       )t

Upvotes: 1

user330315
user330315

Reputation:

You can't calculate the average of a timestamp - after all what is the average of "Monday Afternoon, Tuesday morning and Thursday at 5"?

You need to average the difference between the timestamps. In Postgres subtracting one timestamp from another yields an interval and on that you can apply the avg() aggregate. To get the difference, you can use a window function:

The following is for Postgres.

select avg(diff)
from (
   select starttime - lag(starttime) over (order by starttime) as diff
   from the_table
) t

For MySQL, you need to find the equivalent operator to calculate the difference between two timestamps.

Upvotes: 1

Related Questions