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

12
votes

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).

6
sqlpostgresqldatetime
Why do you want to avoid a function? – MatBailie
I would recommend using a function, so you have one point of update if at some point changes are required, however if you want to avoid it, simply replace $1 with date field – user497849
So do you want to include the last day of the last month? – Erwin Brandstetter
@ErwinBrandstetter Yes, that's fine. – Huuuze

6 Answers

20
votes

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.

6
votes

For getting date of previous/last month:

SELECT (date_trunc('month', now())::date - 1) as last_month_date

Result: 2012-11-30

For getting number of days of previous/last month:

SELECT DATE_PART('days', date_trunc('month', now())::date - 1) last_month_days

Result: 30

2
votes

Try this:

SELECT ...
WHERE  date_field between (date_trunc('MONTH', now()) - INTERVAL '1 day')::date 
                      and now()::date 
       ...
0
votes

Try

select current_date - cast((date_part('day', current_date) + 1) as int)
0
votes

take from http://wiki.postgresql.org/wiki/Date_LastDay, and modified to return just the days in a month

    CREATE OR REPLACE FUNCTION calc_days_in_month(date)
    RETURNS double precision AS
    $$
      SELECT EXTRACT(DAY FROM (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1         day')::date);
    $$ LANGUAGE 'sql' IMMUTABLE STRICT;


    select calc_days_in_month('1999-05-01')

returns 31

0
votes

Reference is taken from this blog:

You can use below function:

CREATE OR REPLACE FUNCTION fn_GetLastDayOfMonth(DATE)
RETURNS DATE AS
$$
    SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::DATE;
$$ LANGUAGE 'sql' 
IMMUTABLE STRICT;

Sample executions:

SELECT *FROM fn_GetLastDayOfMonth(NOW()::DATE);