postgresql 如何获得一个月的天数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6927113/
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
How to get the number of days in a month?
提问by mmrs151
I am trying to get the following in Postgres:
我试图在 Postgres 中获得以下内容：
select day_in_month(2);
Expected output:
预期输出：
28
Is there any built-in way in Postgres to do that?
Postgres 中是否有任何内置方法可以做到这一点？
回答by Quassnoi
SELECT
DATE_PART('days',
DATE_TRUNC('month', NOW())
+ '1 MONTH'::INTERVAL
- '1 DAY'::INTERVAL
)
Substitute NOW()with any other date.
替换NOW()为任何其他日期。
回答by Erwin Brandstetter
Using the smart "trick" to extract the daypart from the last date of the month, as demonstrated by Quassnoi. But it can be a bit simpler / faster:
使用智能“技巧”从当月的最后一天提取日期部分，如Quassnoi 所示。但它可以更简单/更快：
SELECT extract(days FROM date_trunc('month', now()) + interval '1 month - 1 day');
Rationale
基本原理
extractis standard SQL, so maybe preferable, but it resolves to the same function internally as date_part(). The manual:
extract是标准 SQL，所以可能更可取，但它在内部解析为与date_part(). 手册：
The
date_partfunction is modeled on the traditional Ingres equivalent to the SQL-standard functionextract:
该
date_part函数以等效于 SQL 标准函数的传统 Ingres 为模型extract：
But we only need to add a singleinterval. Postgres allows multiple time units at once. The manual:
但是，我们只需要添加一个单一的interval。Postgres 允许同时使用多个时间单位。手册：
intervalvalues can be written using the following verbose syntax:
[@]quantity unit[quantity unit...] [direction]where
quantityis a number (possibly signed);unitismicrosecond,millisecond,second,minute,hour,day,week,month,year,decade,century,millennium, or abbreviations or plurals of these units;
interval可以使用以下详细语法编写值：
[@]quantity unit[quantity unit...] [direction]哪里
quantity是一个数字（可能有签名）；unit是microsecond,millisecond,second,minute,hour,day,week,month,year,decade,century,millennium, 或这些单位的缩写或复数形式；
ISO 8601 or standard SQL formatare also accepted. Either way, the manual again:
也接受ISO 8601 或标准 SQL 格式。无论哪种方式，手册再次：
Internally
intervalvalues are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the seconds field can store fractions.
内部
interval值存储为月、日和秒。这样做是因为一个月中的天数各不相同，如果涉及夏令时调整，一天可能有 23 或 25 小时。月份和日期字段是整数，而秒字段可以存储分数。
(Output / display depends on the setting of IntervalStyle.)
（输出/显示取决于 的设置IntervalStyle。）
The above example uses default Postgres format: interval '1 month - 1 day'. These are also valid (while less readable):
上面的例子使用默认的 Postgres 格式：interval '1 month - 1 day'. 这些也是有效的（虽然可读性较差）：
interval '1 mon - 1 d' -- unambiguous abbreviations of time units are allowed
IS0 8601 format:
IS0 8601 格式：
interval '0-1 -1 0:0'
Standard SQL format:
标准 SQL 格式：
interval 'P1M-1D';
All the same.
全部都一样。
回答by Laurent B
Note that expected output for day_in_month(2) can be 29 because of leap years. You might want to pass a date instead of an int.
请注意，由于闰年，day_in_month(2) 的预期输出可能是 29。您可能想要传递日期而不是 int。
Also, beware of daylight saving : remove the timezone or else some monthes calculations could be wrong (next example in CET / CEST) :
另外，请注意夏令时：删除时区，否则某些月份的计算可能是错误的（CET / CEST 中的下一个示例）：
SELECT DATE_TRUNC('month', '2016-03-12'::timestamptz) + '1 MONTH'::INTERVAL
- DATE_TRUNC('month', '2016-03-12'::timestamptz) ;
------------------
30 days 23:00:00
SELECT DATE_TRUNC('month', '2016-03-12'::timestamp) + '1 MONTH'::INTERVAL
- DATE_TRUNC('month', '2016-03-12'::timestamp) ;
----------
31 days
回答by Mark Mishyn
This works as well.
这也有效。
WITH date_ AS (SELECT your_date AS d)
SELECT d + INTERVAL '1 month' - d FROM date_;
Or just:
要不就：
SELECT your_date + INTERVAL '1 month' - your_date;
These two return interval, not integer.
这两个返回区间，不是整数。
回答by Zbynek Urban
SELECT cnt_dayofmonth(2016, 2); -- 29
create or replace function cnt_dayofmonth(_year int, _month int)
returns int2 as
$BODY$
-- ZU 2017.09.15, returns the count of days in mounth, inputs are year and month
declare
datetime_start date := ('01.01.'||_year::char(4))::date;
datetime_month date := ('01.'||_month||'.'||_year)::date;
cnt int2;
begin
select extract(day from (select (datetime_month + INTERVAL '1 month -1 day'))) into cnt;
return cnt;
end;
$BODY$
language plpgsql;
回答by dr34m3r
You can write a function:
你可以写一个函数：
CREATE OR REPLACE FUNCTION get_total_days_in_month(timestamp)
RETURNS decimal
IMMUTABLE
AS $$
select cast(datediff(day, date_trunc('mon', ), last_day() + 1) as decimal)
$$ LANGUAGE sql;
