Group by days of a month in CockroachDB-CodePudding

In CockroachDB, I want to have such this query on a specific month for its every day:

select count(*), sum(amount) 
from request
where code = 'code_string' 
and created_at >= '2022-07-31T20:30:00Z' and created_at < '2022-08-31T20:30:00Z'

the problem is that I want it on my local date. What should I do? My goal is:

"month, day, count, sum" as result columns for a month.

UPDATE:

I have found a suitable query for this purpose:

select count(amount), sum(amount), extract(month from created_at) as monthTime, extract(day from created_at) as dayTime 
from request 
where code = 'code_string' and created_at >= '2022-07-31T20:30:00Z' and created_at < '2022-08-31T20:30:00Z'
group by dayTime, monthTime

Thanks to @histocrat for easier answer :) by replacing

extract(month from created_at) as monthTime, extract(day from created_at) as dayTime

by this:

date_part('month', created_at) as monthTime, date_part('day', created_at) as dayTime

CodePudding user response：

To group results by both month and day, you can use the date_part function.

select month, day, count(*), sum(things)
from request
where code = 'code_string'
group by date_part('month', created_at) as month, date_part('day', created_at) as day;

Depending on what type created_at is, you may need to cast or convert it first (for example, group by date_part('month', created_at::timestamptz)).