在 postgres 中获取月份的第一个日期 [英] Get first date of month in postgres
问题描述
我正在尝试获取对应于当月第一天的日期"类型.基本上我的一个表存储了一个日期,但我希望它始终是本月的第一个,所以我试图创建一个触发器,它将获取 now() 然后用 1 替换这一天.
您可以使用表达式 date_trunc('month', current_date)
.用 SELECT 语句演示...
select date_trunc('month', current_date)2013-08-01 00:00:00-04
要删除时间,请投射到日期.
select cast(date_trunc('month', current_date) as date)2013-08-01
如果您确定该列应该总是存储一个月的第一天,您还应该使用 CHECK 约束.
创建表 foo (first_of_month 日期不为空检查(从 first_of_month 中提取(天)= 1));插入 foo (first_of_month) 值 ('2015-01-01');--成功插入 foo (first_of_month) 值 ('2015-01-02');--失败
<前>错误:关系foo"的新行违反检查约束foo_first_of_month_check"详细信息:失败的行包含 (2015-01-02).
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.
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).
这篇关于在 postgres 中获取月份的第一个日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!