Get first date of month in postgres
PostgresqlDateTriggersPostgresql Problem Overview
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.
Postgresql Solutions
Solution 1 - Postgresql
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).
Solution 2 - Postgresql
date_trunc()
will do it.
SELECT date_trunc('MONTH',now())::DATE;
http://www.postgresql.org/docs/current/static/functions-datetime.html
Solution 3 - Postgresql
You can also use TO_CHAR to get the first day of the month:
SELECT TO_CHAR(some_date, 'yyyy-mm-01')::date
Solution 4 - Postgresql
Found this to get the first day of that month and the last date of that month
select date_trunc('month', current_date-interval '1 year'), date_trunc('month', current_date-interval '1 year')+'1month'::interval-'1day'::interval;
Solution 5 - Postgresql
SELECT TO_DATE('2017-12-12', 'YYYY-MM-01');
> 2017-12-01