Menu
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.
713
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.
Use the PostgreSQL DATE_PART() function to retrieve the number of the day of a year from a date/time/datetime/timestamp column. This function takes two arguments. The first argument is the date part (or time part) you want the function to return. In this example, we're using ' doy ', which returns the day of the year.
Current month: SELECT date_part('month', (SELECT current_timestamp)); Current day: SELECT date_part('day', (SELECT current_timestamp)); Current hour: SELECT date_part('hour', (SELECT current_timestamp)); Current minute: SELECT date_part('minute', (SELECT current_timestamp));
In PostgreSQL, DATE_TRUNC Function is used to truncate a timestamp type or interval type with specific and high level of precision. Syntax: date_trunc('datepart', field) The datepart argument in the above syntax is used to truncate one of the field,below listed field type: millennium. century.
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).
183
date_trunc() will do it.
SELECT date_trunc('MONTH',now())::DATE; http://www.postgresql.org/docs/current/static/functions-datetime.html
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
