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.

Inamorata

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

Finkle

I used to do it like this select cast((now() - (extract(day from now())) * interval '1 day') + interval '1 day' as date) as first_of_month but this helped. Thanks! – Salivate

Answer 2

date_trunc() will do it.

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

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

Transitory

Answer 3

You can also use TO_CHAR to get the first day of the month:

SELECT TO_CHAR(some_date, 'yyyy-mm-01')::date

Ineducable

Answer 4

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;

Horacehoracio

Why -interval '1 year'? – Oys

That is for getting previous year, first and last day of a month based on the current time. It can be removed to get the current year like select date_trunc('month', current_date-interval '1 year'), date_trunc('month', current_date)+'1month'::interval-'1day'::interval; – Horacehoracio

Answer 5

SELECT TO_DATE('2017-12-12', 'YYYY-MM-01');

2017-12-01

Equidistance

What is the problem with this answer? – Equidistance

probably just that you used a static data rather than the CURRENT_DATE()? – Singletary

that is the problem: LINE 1: SELECT TO_DATE("t1"."date", 'YYYY-MM-01') AS ddate, Sum("t1"... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. – Loki

You should use to_char instead e.g. SELECT TO_CHAR('2017-12-12', 'YYYY-MM-01')::date – Ineducable

Answer 6

date_trunc() always returns the first day of provided type.

for example

SELECT date_trunc('MONTH',now())::DATE will return the first day of month;

SELECT date_trunc('YEAR',now())::DATE will return the first day of year;

SELECT date_trunc('CENTURY',now())::DATE will return the first day of century;

::DATE changes the format of returned data to date format YYYY-MM-DD

Kaolinite