CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
Lars929
Lars929

Reputation: 3

SQL: Selecting Average Hour From DateTime

I am looking to determine the average time a customer takes an action. I have 2 columns, customer_id and Action_timestamp.

Sample data:

customer_id  |  Action_timestamp
1            | 2016-11-21T21:56:49.0000000
1 | 2016-12-07T00:19:34.0000000
1 | 2016-11-21T21:43:19.0000000
2 | 2016-11-21T21:53:39.0000000
3 | 2016-11-21T21:57:33.0000000
3 | 2016-10-01T16:24:10.0000000

I want to group by the customer_id and find the average time from the date timestamp. Average time would equal the sum of all hours (OK to ignore minutes) divided by the count of rows each customer ID appear in.

I'm looking to return: 

1 | 14
2 | 21
3 | 18

Upvotes: 0

Views: 1136

Answers (1)

Shailesh
Shailesh

Reputation: 2276

This should work: 

select customer_id,avg(extract(hour from Action_timestamp)) from <table_name> group by customer_id;

Upvotes: 2

Related Questions