CODEWITHSUNDEEP
postgresql
LeoSam
LeoSam

Reputation: 4951

count data in current month - not 30 days back Postgres statment

Ive this query which return data for 30 days from current date , need to modify it to return data for current month only not 30 days from current date

SELECT count(1) AS counter FROM users.logged WHERE createddate >= date_trunc('month', CURRENT_DATE);

any tips how to tweak this query , at based on Postgres

regards

Upvotes: 2

Views: 7658

Answers (4)

Widada
Widada

Reputation: 595

This is will give you data for the current month only. I try to extract month and year. The last step is you can compare created date against current date-time.

SELECT count(1) AS counter 
FROM users.logged 
WHERE 
    EXTRACT(MONTH FROM createddate) = EXTRACT(MONTH FROM current_date)
    AND EXTRACT(YEAR FROM createddate) = EXTRACT(YEAR FROM current_date);

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78523

Ive this query which return data for 30 days from current date , need to modify it to return data for current month only not 30 days from current date

That's incorrect. Your query:

SELECT count(1) AS counter FROM users.logged WHERE createddate >= date_trunc('month', CURRENT_DATE);

returns all dates >= Nov 1st 00:00:00, in other words what you say that you want already. Or then, you've simplified your query and left out the more important bits — those that are broken. If not:

It might be that you've dates in the future and that you're getting incorrect counts as a result. If so, add an additional criteria in the where clause:

AND created_date < date_trunc('month', CURRENT_DATE) + interval '1 month'

It might also be that your sample data has a bizarre row with a time zone such that it looks like the timestamp is from this month but the date/time arithmetics land it last month.

Upvotes: 1

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95682

Something like this should work.

SELECT count(1) AS counter
FROM users.logged
WHERE date_trunc('month', createddate) = date_trunc('month', current_date);

Upvotes: 10

Etherealone
Etherealone

Reputation: 3558

It is already supposed to return the values in current month. Truncation does the conversion 10 Nov 2013 14:16 -> 01 Nov 2013 00:00 and it will return the data since the beginning of this month. The problem seems to be something else.

Upvotes: 1

Related Questions