extract in postgresql date

Question

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.

harmic is correct. In case you have some kind of data warehouse, you can add new column to time dimension. For example we do have column is_previous_month boolean in our time dimension. — Tomas Greif
– Tomas Greif, Commented Jan 24, 2014 at 13:06

harmic · Accepted Answer · 2014-01-24 11:20:13Z

2

How about this:

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

answered Jan 24, 2014 at 11:20

harmic

31k6 gold badges75 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sjoerdjump Over a year ago

Ah Thank you very much. I see what you are getting at. I'm using jaspersoft report and in a report i can add the columns and create variables corresponding to the columns. Thanks a lot! (I don't have 15 rep so I can't vote your answer up. .)

harmic Over a year ago

@Sjoerdjump if this answer works for you, you should be able to accept it even without any rep

Sjoerdjump Over a year ago

tested it and it worked like a charm :) Thanks again!

Collectives™ on Stack Overflow

extract in postgresql date

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related