postgresql 在postgres中获取月份的第一个日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18069275/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get first date of month in postgres
提问by Gargoyle
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.
我正在尝试获取对应于当月第一天的“日期”类型。基本上我的一个表存储了一个日期，但我希望它始终是本月的第一个，所以我试图创建一个触发器，它将获取 now() 然后用 1 替换这一天。
回答by Mike Sherrill 'Cat Recall'
You can use the expression date_trunc('month', current_date). Demonstrated with a SELECT statement . . .
您可以使用表达式date_trunc('month', current_date)。用 SELECT 语句演示。. .
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 alwaysstore only the first of a month, you should also use a CHECK constraint.
如果您确定该列应该始终只存储一个月的第一天，您还应该使用 CHECK 约束。
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).
回答by bma
date_trunc()will do it.
date_trunc()会做的。
SELECT date_trunc('MONTH',now())::DATE;
http://www.postgresql.org/docs/current/static/functions-datetime.html
http://www.postgresql.org/docs/current/static/functions-datetime.html
回答by Naufal
You can also use TO_CHAR to get the first day of the month:
您还可以使用 TO_CHAR 获取该月的第一天：
SELECT TO_CHAR(some_date, 'yyyy-mm-01')::date
回答by JyotiKumarPoddar
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;
回答by Victorqedu
SELECT TO_DATE('2017-12-12', 'YYYY-MM-01');
SELECT TO_DATE('2017-12-12', 'YYYY-MM-01');
2017-12-01
2017-12-01
