How to Get the Last Day of Month in Postgres

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

How to get current month firstdate and lastdate in postgres sql query

First day is easy...

SELECT date_trunc('month', CURRENT_DATE);

Last day isn't much more difficult either.

SELECT date_trunc('month', CURRENT_DATE) + interval '1 month - 1 day';

Is there a way to preserve last day of month while adding interval?

Perhaps this function can make you happy:

CREATE OR REPLACE FUNCTION add_months_preserve_end(d date, i interval)
   RETURNS date
   LANGUAGE sql IMMUTABLE STRICT AS
$$SELECT
   CASE WHEN date_trunc('month', d) + INTERVAL '1 month' = d + INTERVAL '1 day'
             AND i = date_trunc('month', i)
        THEN (date_trunc('month', d) + i + INTERVAL '1 month -1 day')::date
        ELSE (d + i)::date
   END$$;

It treats days at the end of the month differently.

Get first date of month in postgres

You can use the expression date_trunc('month', current_date). Demonstrated with a SELECT statement . . .

select date_trunc('month', current_date)
2013-08-01 00:00:00-04

To remove time, cast to date.

select cast(date_trunc('month', current_date) as date)
2013-08-01

If you're certain that column should always store only the first of a month, you should also use a CHECK constraint.

create table foo (
  first_of_month date not null
  check (extract (day from first_of_month) = 1)
);

insert into foo (first_of_month) values ('2015-01-01'); --Succeeds
insert into foo (first_of_month) values ('2015-01-02'); --Fails

ERROR:  new row for relation "foo" violates check constraint "foo_first_of_month_check"
DETAIL:  Failing row contains (2015-01-02).

How to write function in postgresql to get months starting date and end date on the basis of name of the month passed to it

demo:db<>fiddle

CREATE OR REPLACE FUNCTION get_month_start_and_end(_month text) 
RETURNS TABLE (start_date date, end_date date) 
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        _start_date,
        (_start_date + interval '1 month -1 day')::date
    FROM (
        SELECT 
            to_date(_month || date_part('year', CURRENT_DATE), 'MonthYYYY') AS _start_date
    ) s;
END;
$$ LANGUAGE plpgsql;

1. Get current year: date_part('year', CURRENT_DATE)
2. Get date from month and year: to_date(<month text> || <year from 1.>, 'MonthYYYY'). This gives the first of month because no day value is given
3. Calculate the end day of month as you already described
4. Give out the two columns

---

Related Topics