Postgresql日期函数以可读的格式返回范围

Question

我想知道是否可以使用postgresql的日期时间函数、concat函数和case函数给出以下结果之一：

• 2019年6月4日至11日(第1和第2天在同一个月内)
• 2019年7月27日至7月3日(第一和第二天在不同的日历月份，但同一历年)
• 2019年12月29日至2020年1月4日(第1和第2天在不同日历月和不同年份)

我正着手创建一个视图，用于在每周提示更改时发布状态更新(如Twitter)。在我的编程中，我尽可能地使用数据库本机函数。在理想的世界里，如果postgresql能够自己做到这一点，我就不会使用源代码来完成这个任务。

到目前为止创建的对视图的SELECT查询是：

SELECT 'chronic pain'::text AS section,
    'https://rons-home.net/en/living-life-lab/tips/living-with-chronic-pain'::text AS link,
    tips_chronic_pain.reference,
    tips_chronic_pain.tip,
    tips_chronic_pain_weekly_selection.start_date,
    tips_chronic_pain_weekly_selection.end_date
FROM tips_chronic_pain
JOIN tips_chronic_pain_weekly_selection ON tips_chronic_pain.reference = tips_chronic_pain_weekly_selection.tips_chronic_pain_reference
ORDER BY tips_chronic_pain_weekly_selection.start_date DESC
LIMIT 1

我创建了以下两个表：

表1包含所有提示：

CREATE TABLE public.tips_chronic_pain
(
  reference bigint NOT NULL DEFAULT nextval('tips_chronic_pain_reference_seq'::regclass),
  tip text,
  add_date timestamp with time zone DEFAULT now(), -- UTC
  membership_reference bigint,
  CONSTRAINT tips_chronic_pain_pkey PRIMARY KEY (reference)
)

表2确定当前显示的提示：

CREATE TABLE public.tips_chronic_pain_weekly_selection
(
  reference bigint NOT NULL DEFAULT nextval('tips_chronic_pain_weekly_selection_reference_seq'::regclass),
  tips_chronic_pain_reference bigint,
  start_date timestamp with time zone DEFAULT now(), -- UTC
  end_date timestamp with time zone DEFAULT now(), -- UTC
  CONSTRAINT tips_chronic_pain_weekly_selection_pkey PRIMARY KEY (reference)
)

• 列tips_chronic_pain_weekly_selection.start_date提供了第一个日期
• 列tips_chronic_pain_weekly_selection.end_date提供了第二个日期

Answer 1

CREATE OR REPLACE FUNCTION readable_date_range(DATERANGE)
RETURNS TEXT AS $

  SELECT CASE WHEN to_char(LOWER($1), 'YYYY-MM') = to_char(UPPER($1), 'YYYY-MM')
              THEN to_char(LOWER($1), 'FMMonth FMDDth to ') || to_char(UPPER($1), 'FMDDth YYYY')
              WHEN EXTRACT(year FROM LOWER($1)) = EXTRACT(year FROM UPPER($1))
              THEN to_char(LOWER($1), 'FMMonth FMDDth to ') || to_char(UPPER($1), 'FMMonth FMDDth YYYY')
              ELSE to_char(LOWER($1), 'FMMonth FMDDth YYYY to ') || to_char(UPPER($1), 'FMMonth FMDDth YYYY')
              END;

$ LANGUAGE SQL STRICT IMMUTABLE;

让我们看看一些输出：

[[local]:5432] user =# SELECT readable_date_range('[2019-01-01,2019-05-03)');
     readable_date_range     
─────────────────────────────
 January 1st to May 3rd 2019
(1 row)

Time: 63.280 ms
[[local]:5432] user =# SELECT readable_date_range('[2019-01-01,2019-01-03)');
   readable_date_range   
─────────────────────────
 January 1st to 3rd 2019
(1 row)

Time: 0.668 ms
[[local]:5432] user =# SELECT readable_date_range('[2019-01-01,2022-01-03)');
         readable_date_range          
──────────────────────────────────────
 January 1st 2019 to January 3rd 2022
(1 row)

Time: 4.563 ms

您可以很容易地将其修改为采用两个值。