Postgresql Date Function Returning Range In Readable Format

caseconcatdate formatpostgresql

I am wondering if it is possible to use postgresql's date-time , concat and case functions to give one of the following results:

  • June 4th to 11th 2019 (where the 1st and 2nd date are within the same month)
  • July 27th to July 3rd 2019 (where the 1st and 2nd date are in different calendar months but the same calendar year)
  • December 29th 2019 to January 4th 2020 (where the 1st and 2nd date are in different calendar months and different years)

I am setting out to create a VIEW that will be used for posting status updates (such as to Twitter) when the weekly tip is changed. In my programming I endeavor to use the databases native functions to the most possible. In an ideal world I wouldn't use source code to accomplish this provided postgresql is able to do so itself.

The SELECT query for VIEW that I've created so far is:

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

I have created the following 2 tables:

Table #1 contains all the tips:

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)
)

Table #2 determines which tip is currently being displayed:

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)
)
  • Column tips_chronic_pain_weekly_selection.start_date provides the first date
  • Column tips_chronic_pain_weekly_selection.end_date provides the second date

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;

Let's look at some output:

[[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

You could quite easily modify that to take two values instead.

Related Question