Get Average of Second in PostgreSQL

Known:

I have one table which name is table_product_qrcode like image below

Wanted:

I want to count average of second from time_active column

Question:

How to do it … ?

Answer:

I make query with lead function like this one

select lead(time_active)OVER(order by time_active) as waktu_selanjutnya, time_active, age(lead(time_active)OVER( order by time_active), time_active) detik from table_product_qrcode where time_active::date = current_date

Explain:

  • lead function is used to get next row
  • age function is to subtract between two timestamp ( waktu_selanjutnya – time_active )

Result:

Convert Time to Second

select lead(time_active)OVER(order by time_active) as waktu_selanjutnya, time_active, extract(epoch from(age(lead(time_active)OVER( order by time_active), time_active))) detik from table_product_qrcode where  time_active::date = current_date

Explain:

  • extract(epoch from time) is used to convert to second

Result:

Get Average of Second

select avg(tb2.detik)::integer detik from (select lead(time_active)OVER(order by time_active) as waktu_selanjutnya, time_active, extract(epoch from age(lead(time_active)OVER(order by time_active), time_active)) detik  from table_product_qrcode)tb2 where time_active::date = current_date

Result:

Thanks for reading

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *