Get average for "last month" only

Pretty new to SQL and have hit a roadblock.

I have this query, which works fine:

SELECT 
(COUNT(*)::float / (current_date - '2017-05-17'::date)) AS "avg_per_day"
FROM "table" tb;

I now want it to include only data from the last month, not all time.

I've tried doing something along the lines of:

SELECT 
(COUNT(*)::float / (current_date - (current_date - '1 month' ::date)) AS "avg_per_day"
FROM "table" tb;

The syntax is clearly wrong, but I am not sure what the right answer is. Have googled around and tried various options to no avail.

I can't use a simple AVG because the number I require is an AVG per day for the last month of data. Thus I've done a count of rows divided by the number of days since the first occurrence to get my AVG per day.

I have a column which tells me the date of the occurrence, however there are multiple rows with the same date in the dataset. e.g.

created_at 
----------------------------
Monday 27th June 2017 12:00 
Monday 27th June 2017 13:00 
Tuesday 28th June 2017 12:00

and so on.

I am counting the number of occurrences per day and then need to work out an average from that, for the last month of results only (they date back to May).

Solution

The answer depends on the exact definition of "last month" and the exact definition of "average count".

Assuming:

Your column is defined created_at timestamptz NOT NULL
You want the average number of rows per day - days without any rows count as 0.
Cover 30 days exactly, excluding today.

SELECT round(count(*)::numeric / 30, 2)  -- simple now with a fixed number of days
FROM   tbl
WHERE  created_at >= (now()::date - 30)
AND    created_at <   now()::date        -- excl. today

Rounding is optional, but you need numeric instead of float to use round() this way.

Not including the current day ("today"), which is ongoing and may result in a lower, misleading average.

If "last month" is supposed to mean something else, you need to define it exactly. Months have between 28 and 31 days, this can mean various things. And since you obviously operate with timestamp or timestamptz, not date, you also need to be aware of possible implications of the time of day and the current time zone. The cast to date (or the definition of "day" in general) depends on your current timezone setting while operating with timestamptz.