getting daily stats for event counts

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

getting daily stats for event counts

David Gauthier
Hi:  
9.6.0 on linux

I have a table which logs the inception of an event with an "incept_datetime" (timestamptz) field.  I want to know how many events occurred per day from one date to the next using midnight as a marker for each day reported.  The end result should be something like...

2019-06-23  44
2019-06-24  18
2019-06-25  38
etc...

Is there a query that can do this ?

Reply | Threaded
Open this post in threaded view
|

Re: getting daily stats for event counts

Tom Lane-2
David Gauthier <[hidden email]> writes:
> I have a table which logs the inception of an event with an
> "incept_datetime" (timestamptz) field.  I want to know how many events
> occurred per day from one date to the next using midnight as a marker for
> each day reported.  The end result should be something like...

> 2019-06-23  44
> 2019-06-24  18
> 2019-06-25  38
> etc...

> Is there a query that can do this ?

Something involving GROUP BY date_trunc('day', incept_datetime)
ought to do it, possibly with some fooling around required
depending on what definition of "midnight" you want to use.

                        regards, tom lane