如何获取一个月中有多少天？

发布于 2024-11-27 17:12:05 字数 159 浏览 4 评论 0原文

我试图在 Postgres 中获得以下内容：

select day_in_month(2);

预期输出：

Postgres 中是否有任何内置方法可以做到这一点？

原文

I am trying to get the following in Postgres:

select day_in_month(2);

Expected output:

Is there any built-in way in Postgres to do that?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

薄荷梦 2024-12-04 17:12:05

SELECT  
    DATE_PART('days', 
        DATE_TRUNC('month', NOW()) 
        + '1 MONTH'::INTERVAL 
        - '1 DAY'::INTERVAL
    )

将 NOW() 替换为任何其他日期。

SELECT  
    DATE_PART('days', 
        DATE_TRUNC('month', NOW()) 
        + '1 MONTH'::INTERVAL 
        - '1 DAY'::INTERVAL
    )

Substitute NOW() with any other date.

回复收藏 0 原文

ま昔日黯然 2024-12-04 17:12:05

使用智能“技巧”从该月的最后一个日期中提取天部分，如Quassnoi 演示的。但它可以更简单/更快一些：

SELECT extract(days FROM date_trunc('month', now()) + interval '1 month - 1 day');

基本原理

extract 是标准 SQL，因此可能更可取，但它在内部解析为与 date_part() 相同的函数。手册：

date_part 函数以传统的 Ingres 为模型，相当于 SQL 标准函数 extract：

但我们只需要添加一个单个 间隔。 Postgres 允许同时使用多个时间单位。手册：

interval 值可以使用以下详细语法编写：
[@]数量单位[数量单位< /em>...] [方向]
其中数量是一个数字（可能有符号）； 单位是微秒，
毫秒、秒、分钟、小时、天、周< /code>、月、年、十年、世纪、千年或这些单位的缩写或复数；

ISO 8601 或标准 SQL 格式也被接受。无论哪种方式，再次查看手册：

内部间隔值存储为月、日和秒。
这样做是因为一个月中的天数不同，并且一天
如果夏令时调整为 23 或 25 小时
涉及。月份和日期字段是整数，而秒字段是
字段可以存储分数。

（输出/显示取决于设置 < code>IntervalStyle。）

上面的示例使用默认 Postgres 格式：interval '1 个月 - 1 天'。这些也是有效的（虽然可读性较差）：

interval '1 mon - 1 d' -- unambiguous abbreviations of time units are allowed

IS0 8601 格式：

interval '0-1 -1 0:0'

标准 SQL 格式：

interval 'P1M-1D';

都一样。

Using the smart "trick" to extract the day part from the last date of the month, as demonstrated by Quassnoi. But it can be a bit simpler / faster:

SELECT extract(days FROM date_trunc('month', now()) + interval '1 month - 1 day');

Rationale

extract is standard SQL, so maybe preferable, but it resolves to the same function internally as date_part(). The manual:

The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract:

But we only need to add a single interval. Postgres allows multiple time units at once. The manual:

interval values can be written using the following verbose syntax:
[@]quantity unit[quantity unit...] [direction]
where quantity is a number (possibly signed); unit is microsecond,
millisecond, second, minute, hour, day, week, month, year, decade,
century, millennium, or abbreviations or plurals of these units;

ISO 8601 or standard SQL format are also accepted. Either way, the manual again:

Internally interval values 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.

(Output / display depends on the setting of IntervalStyle.)

The above example uses default Postgres format: interval '1 month - 1 day'. These are also valid (while less readable):

interval '1 mon - 1 d' -- unambiguous abbreviations of time units are allowed

IS0 8601 format:

interval '0-1 -1 0:0'

Standard SQL format:

interval 'P1M-1D';

All the same.

回复收藏 0 原文

烟雨扶苏 2024-12-04 17:12:05

请注意，由于闰年，day_in_month(2) 的预期输出可能为 29。您可能想传递日期而不是整数。

另外，请注意夏令时：删除时区，否则某些月份的计算可能会错误（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

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.

Also, beware of daylight saving : remove the timezone or else some monthes calculations could be wrong (next example in 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

回复收藏 0 原文

-黛色若梦 2024-12-04 17:12:05

SELECT
    (
        timestamp '2024-01-01' + INTERVAL '1 month'
    ) - timestamp '2024-01-01' AS days
-- 31 days

两个时间戳相减将始终产生以天为单位的间隔。我们不会仅仅得到以月或年为单位的答案，因为这些时间段的长度是可变的。

SELECT
    (
        timestamp '2024-01-01' + INTERVAL '1 month'
    ) - timestamp '2024-01-01' AS days
-- 31 days

The subtracting two timestamps will always produce an interval in terms of days. We won't just get an answer in terms of months or years, because the length of those time periods is variable.

回复收藏 0 原文

书信已泛黄 2024-12-04 17:12:05

这也有效。

WITH date_ AS (SELECT your_date AS d)
SELECT d + INTERVAL '1 month' - d FROM date_;

或者只是：

SELECT your_date + INTERVAL '1 month' - your_date;

这两个返回间隔，而不是整数。

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.

回复收藏 0 原文

貪欢 2024-12-04 17:12:05

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;

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;

回复收藏 0 原文

别低头，皇冠会掉 2024-12-04 17:12:05

你可以写一个函数：

CREATE OR REPLACE FUNCTION get_total_days_in_month(timestamp)
RETURNS decimal
IMMUTABLE
AS $
  select cast(datediff(day, date_trunc('mon', $1), last_day($1) + 1) as decimal)
$ LANGUAGE sql;

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', $1), last_day($1) + 1) as decimal)
$ LANGUAGE sql;

回复收藏 0 原文

~没有更多了~