Getting all dates from previous 1 month, or 2 months in PostgreSQL

postgresql, postgresql-9.1, reporting-services

Solution

You likely want something like:

SELECT *
FROM the_table
WHERE date_column BETWEEN '2013-01-01' AND '2013-01-01' + INTERVAL '1' MONTH;

The `BETWEEN` operator is left-and-right inclusive. If you want right-exclusive you have to use separate `>=` and `<` tests instead.

Except for the `date_trunc` function that's ANSI-standard SQL, by the way, it's just that Microsoft SQL Server doesn't implement the ANSI interval types or ANSI date maths. Doing it a strictly ANSI-standard way would require you to replace the PostgreSQL-specific `date_trunc` with interval maths, like this example of how to get the months I just cooked up in Oracle. It's based on a subset of the sample data because of limitations in SQLFiddle for Oracle.

If you want to get the start of the current month, use `date_trunc`, eg:

SELECT date_trunc('2013-01-12');

will return `2013-01-01`. This can be combined with `INTERVAL` computations and the `extract` operation to do pretty much anything you need to with dates and times.

In this case, for the month before last I'd write:

SELECT *
FROM the_table
WHERE date_field BETWEEN date_trunc('month',current_date) - INTERVAL '2' MONTH 
                     AND (date_trunc('month',current_date) - INTERVAL '1' MONTH) - INTERVAL  '1' DAY;

(You want `INTERVAL '1' SECOND` instead of `INTERVAL '1' DAY` if you're working with timestamps not dates).

You'll notice the explicit parens for the interval subtraction. That's quite important, because dates are horrible nasty things. In date computations, `(a + b) + c` isn't necessarily the same as `a + (b + c)`. I was bitten by this recently, it's nasty.

Alternate phrasing, probably cleaner if you want `a <= x < b`:

SELECT *
FROM the_table
WHERE date_field >= date_trunc('month',current_date) - INTERVAL '2' MONTH
  AND date_field < date_trunc('month',current_date) - INTERVAL '1' MONTH;

Problem

Original source