Get first date of month in postgres

Question

I'm trying to get a 'date' type that corresponds to the first day of the current month. Basically one of my tables stores a date, but I want it to always be the first of the month, so I'm trying to create a trigger that will get now() and then replace the day with a 1.

Answer 1

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

Answer 2

date_trunc() will do it.

SELECT date_trunc('MONTH',now())::DATE; 

http://www.postgresql.org/docs/current/static/functions-datetime.html