SQL query : aggregating records having a timestamped field, where fields timestamp falls in same tim-CodePudding

I have a database table customers that records among others the arrival time of clients for a one month data. So, the customers table contains among other a column named arrival_time . (e.g., arrival_time = 12/1/2020 12:01:39 AM, arrival_time = 12/1/2020 12:01:34 AM etc…) Is it possible to design/write an SQL query that returns the numbers of customers that arrived each second (or say, min, hour...) in this this one month data.

Thank you.

CodePudding user response：

If you are using PostgreSQL you can use DATE_TRUNC(). For example:

select
  date_trunc('minute', arrival_time) as at,
  count(distinct customer_id) as cnt
from t
group by date_trunc('minute', arrival_time)

You can change 'minute' for 'day', 'week', 'second', etc. See https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC