主页 / dba / 问题 / 243748
Accepted
Ron Piggott
Asked: 2019-07-25 20:29:56 +0800 CST

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

  • 772

我想知道是否可以使用 postgresql 的 date-time 、 concat 和 case 函数来给出以下结果之一：

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

我打算创建一个视图，用于在每周提示更改时发布状态更新（例如到 Twitter）。在我的编程中，我尽量使用数据库的原生函数。在一个理想的世界里，我不会使用源代码来完成这个，前提是 postgresql 本身能够做到这一点。

到目前为止，我创建的 VIEW 的 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

我创建了以下 2 个表：

表 #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 提供第二个日期
postgresql case

1 个回答

  1. Best Answer
    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

    您可以很容易地修改它以取两个值。

    • 2

相关问题