How to Determine the Last Day of the Previous Month Using Postgresql

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

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.

Get data for previous month in postgresql

Subtract one month from the current month, then "truncate" that to the beginning of that date. As you don't want to include rows from "this" month, you also need to add a condition for that

SELECT *
FROM Conference 
WHERE date_start >= date_trunc('month', current_date - interval '1' month)
  and date_start < date_trunc('month', current_date)

date_trunc('month', current_date - interval '1' month) will return the 1st day of the previous month and date_trunc('month', current_date) will return the first day of "this" month.

postgresql : how to get previous month?

demo:db<>fiddle

You can use the interval addition/subtraction:

SELECT 
    my_date - interval '1 month'

Note, that this subtraction gives out a type timestamp. This can be casted into a date of course:

SELECT 
    (my_date - interval '1 month')::date AS prev_month

---

The solution above always subtracts one month. So, if you had 2019-04-04 you would get 2019-03-04. If you want to get the the first of previous month regardless of the current day, you can strip the day to the first of the current month before using date_trunc():

demo:db<>fiddle

SELECT
    date_trunc('month', my_date)

results in 2019-04-01 for given date 2019-04-05, e.g. And with that result, you are able to subtract one month to get the first of the previous month:

SELECT 
    my_date,
    (date_trunc('month', my_date) - interval '1 month')::date as prev_month
FROM
    my_table

How to get the last day of month in postgres?

If you're using Amazon AWS Redshift then you can use Redshift's LAST_DAY function. While Redshift is based on PostgreSQL, the LAST_DAY function is not available in PostgreSQL, for a solution for PostgreSQL see @wspurgin's answer.

https://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html

LAST_DAY( { date | timestamp } )

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the data type of the date argument.

For example:

SELECT LAST_DAY( TO_DATE( act_date, 'YYYYMMDD' ) )

Get first date of previous month

Try:

make_interval(month := 1)
'1 month'::interval
cast('1 month' as interval)

instead of interval '1 month'

How to find the last 'working' day of last month?

Piggy-backing off of Gordon's answer, I think this is what you want:

SELECT (
    date_trunc('month', current_date) - interval '1 day' -
    (case extract(dow from date_trunc('month', current_date) - interval '1 day')
         when 0 then 2
         when 6 then 1
         else 0
      end) * interval '1 day'
)::date as last_weekday_in_last_month;

Assuming your weekends are 0 (Sunday) and 6 (Saturday). It uses OP's original logic to find the last date of the last month, then Gordon's CASE logic to subtract more days if the last date is 0 or 6.

get last three month records from table

You can use built-in INTERVAL instruction

Check how this works:

SELECT CURRENT_DATE - INTERVAL '3 months'

and you can rewrite your SQL to:

SELECT * from table where date >  CURRENT_DATE - INTERVAL '3 months'

(not checked but this should give you an idea how to use INTERVAL instruction)

---

Related Topics