14

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Huuuze
  • 15,528
  • 25
  • 72
  • 91

6 Answers6

22

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'

I use the < operator for the second condition to get precise results (read: "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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This generates the following error: operator does not exist: timestamp with time zone - integer – Huuuze Jan 20 '12 at 17:23
  • @Huunze: should be fixed by now. – Erwin Brandstetter Jan 20 '12 at 17:24
  • I think we're getting closer. This seems to be working, but it's excluding values with today's date. – Huuuze Jan 20 '12 at 17:31
  • Tried this for the 2nd portion of the BETWEEN and it worked: date_trunc('DAY', now())::date+1. Doesn't feel right, but it works. Suggestions? – Huuuze Jan 20 '12 at 17:35
  • @Huuuze: From your comments I deduce that your column (`my_date` in my example) is **not** actually a `date`, but a `timestamp`. You should have provided this information. I amended my answer with an additional solution for timestamps. Note, that the solution you describe in the comment would include "tomorrow, 00:00", which would be wrong. – Erwin Brandstetter Jan 20 '12 at 18:05
  • How can I get the first day *date* of the previous month ? – Arup Rakshit Jun 20 '14 at 06:43
  • 2
    @ArupRakshit: `date_trunc('month', now() - interval '1 month')::date` – Erwin Brandstetter Jun 20 '14 at 10:46
  • @ArupRakshit: Use a *question* next time. Comments are not meant for new questions. You can always link to another answer for reference ... – Erwin Brandstetter Jun 20 '14 at 11:42
6

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

UserBSS1
  • 2,091
  • 1
  • 28
  • 31
2

Try this:

SELECT ...
WHERE  date_field between (date_trunc('MONTH', now()) - INTERVAL '1 day')::date 
                      and now()::date 
       ...
  • 1
    it needs to be just `'-1 day'::interval` for the OP's actual requirement – araqnid Jan 20 '12 at 16:59
  • @araqnid As you noted, the query above isn't working. How do I integrate your suggestion into the suggested query? – Huuuze Jan 20 '12 at 17:06
  • @MarkBannister I have a record that falls between December 31, 2011 and today, but your query does not return it. If I remove your suggestion from my query and replace it with "2011-12-31", the expected result occurs. – Huuuze Jan 20 '12 at 17:08
  • @Huuze: Try the amended formula. –  Jan 20 '12 at 17:23
  • @MarkBannister I must be doing something wrong. Here's my/your WHERE clause: my_date >= (date_trunc('MONTH', my_date) - INTERVAL '1 day')::date and my_date <= now(). This returns results that are dated December 20. Thoughts? – Huuuze Jan 20 '12 at 17:27
  • @huuuze: use `date_trunc('MONTH', now()) - INTERVAL '1 day')` to get the end-of-month before *now* rather than `my_date` – araqnid Jan 20 '12 at 17:30
  • @Huuuze: Yes, I had written it wrong. If this still doesn't work, I'm going to delete this answer and give up. :( –  Jan 20 '12 at 17:33
0

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

user2601381
  • 1
  • 2
0

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);
Anvesh
  • 7,103
  • 3
  • 45
  • 43
0

Try

select current_date - cast((date_part('day', current_date) + 1) as int)
theglauber
  • 28,367
  • 7
  • 29
  • 47