sql - How do I determine the last day of the previous month using PostgreSQL?

Question

I need to query a PostgreSQL database to determine records that fall within today's date and the last day of the previous month. In other words, I'd like to retrieve everything that falls between December 31, 2011 and today. This query will be re-used each month, so next month, the query will be based upon the current date and January 31, 2012.

I've seen this option, but I'd prefer to avoid using a function (if possible).

Answer 1

Both solutions include the last day of the previous month and also include all of "today".

For a date column:

SELECT *
FROM   tbl
WHERE  my_date BETWEEN date_trunc('month', now())::date - 1
               AND     now()::date

You can subtract plain integer values from a date (but not from a timestamp) to subtract days. This is the simplest and fastest way.

For a timestamp column:

SELECT *
FROM   tbl
WHERE  my_timestamp >= date_trunc('month', now()) - interval '1 day'
AND    my_timestamp <  date_trunc('day'  , now()) + interval '1 day'

Note that I use the < operator for the second condition to get precise results (~ "before tomorrow").

I do not cast to date in the second query. Instead I add an interval '1 day', to avoid casting back and forth.

Have a look at date / time types and functions in the manual.