Average timestamp in one column in BigQuery-CodePudding

I need to find the average of the order that came:

Order_Date
2022-06-02 15:40:00 UTC
2022-06-07 11:01:00 UTC
2022-06-21 10:55:00 UTC
2022-06-23 14:44:00 UTC

Outcome:

average Order_Date *that came

CodePudding user response：

Just apply the AVG() average function over your entire table:

SELECT AVG(Order_Date) AS Avg_Order_Date
FROM yourTable;

CodePudding user response：

Average timestamp is unusual ask! But anyway, formally you can do below

select 
  timestamp_seconds(cast(avg(unix_seconds(timestamp(Order_date))) as int64)) as average_Order_Date
from your_table

if applied to sample data in your question - output is

Note: Supported signatures for AVG: AVG(INT64); AVG(FLOAT64); AVG(NUMERIC); AVG(BIGNUMERIC); AVG(INTERVAL) - that is why you need all this back and forth "translations"