通用季度結束日期，PostgreSQL

我想為給定日期生成通用季度結束日期。

例如：如果我有2010-01-01，我想退貨2010-03-31，等等。

我可以得到季度數和年份：

select to_char(date_trunc('quarter',  current_date)::date, 'yyyy-q');

2017-3從今天開始返回的是2017-07-14

我如何很好地獲得季度結束日期？

我可以得到答案，但它非常醜陋：

select to_char(date_trunc('year',  date '2015-01-01'),'yyyy') || '-' ||case
   when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 1 then '03-31'
   when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 2 then '06-30'
   when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 3 then '09-30'
   when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 4 then '12-31'
   else '?'
   end 

自從2015-03-31我放入2015-01-01.

有沒有更好的辦法？

我認為解決這個問題的最短和最優雅的方法是使用**date_trunc('quarter',d)**（將檢索季度開始） + 3 months - 1 day，並使用表達式創建一個FUNCTION：

CREATE FUNCTION end_of_quarter (d date) 
RETURNS date AS
$$
SELECT
   CAST(date_trunc('quarter', d)  + interval '3 months' - interval '1 day' AS date)
$$
LANGUAGE SQL IMMUTABLE ;

…然後使用它：

SELECT
   d, end_of_quarter(d)
FROM
   (SELECT 
       CAST(d0 AS date) AS d 
   FROM 
       generate_series(date '2017-01-01', date '2017-12-31', interval '6 days') AS s(d0)
   ) AS q ;

d | 季度末
:---------- | :-------------
2017-01-01 | 2017-03-31 
...
2017-03-26 | 2017-03-31 
2017-04-01 | 2017-06-30 
...
2017-06-18 | 2017-06-30 
2017-07-06 | 2017-09-30 
2017-07-12 | 2017-09-30 
... 
2017-09-22 | 2017-09-30 
2017-09-28 | 2017-09-30 
2017-10-04 | 2017-12-31 
2017-10-10 | 2017-12-31 
... 
2017-12-21 | 2017-12-31 
2017-12-27 | 2017-12-31 

您可以在此處**的dbfiddle檢查它

---

您還可以通過使用以下簡單版本來縮短原始方法CASE expression WHEN value THEN ...：

SELECT
   d, 
   extract('year' from d) || '-' ||
   /* case expression when value instead of case when expression */
   case extract('quarter' from d)
       when 1 then '03-31'
       when 2 then '06-30'
       when 3 then '09-30'
       else        '12-31'
   end AS end_of_quarter
FROM
   generate_series(date '2017-01-01', date '2017-12-31', interval '6 days') AS s(d) ;

您可以在此處**的dbfiddle檢查它

---

如果您需要經常這樣做，請定義一個函式：

CREATE FUNCTION end_of_quarter (d date) 
RETURNS date AS
$$
SELECT
   cast (extract('year' from d) || '-' ||
       case extract('quarter' from d)
           when 1 then '03-31'
           when 2 then '06-30'
           when 3 then '09-30'
           else        '12-31'
       end
   AS date)
$$
LANGUAGE SQL IMMUTABLE ;

dbfiddle在這裡

引用自：https://dba.stackexchange.com/questions/179874