0

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
2
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.