CODEWITHSUNDEEP
postgresqldate
Sjoerdjump
Sjoerdjump

Reputation: 25

extract in postgresql date

I have a question about extract function in PostgreSQL.

I am generating reports about last month's data.

So far no problem:

where extract (month from date) = extract(month from current_date- '1 month'::interval)

But the problem with this setup is that when the data spans more than 1 year than more months will be included.

To handle this I can add another condition for year:

and extract (year from date) = extract (year from current_date)

But this will cause a problem when generating report about December in January.

How can I generate my report about December in January without the fear that I include more months.

Upvotes: 0

Views: 110

Answers (1)

harmic
harmic

Reputation: 30587

How about this:

WHERE date_trunc('month',date)=date_trunc('month',current_date) - interval '1 month'

Upvotes: 2

Related Questions