Parse String in Postgres

Question

in a PostgreSQL database I have a table with a cell that contains working hours per day and its value is something like this:

---
Fri:
- '8'
- '9'
- '10'
- '11'
- '12'
- '13'
- '14'
- '15'
- '16'
- '17'
Mon:
- '8'
- '9'
... etc ...
Wed:
- '8'
- '9'
- '10'
- '11'
- '12'
- '13'
- '14'
- '15'
- '16'
- '17'

How can I read or put it in another table like this:

dow	start	end
1	08:00:00	18:00:00
2	08:00:00	18:00:00
3	08:00:00	18:00:00

Where dow stands for day of week, for example 1 for monday, 2 for tuesday, etc..

Code Sample to create the starting table:

CREATE TABLE test
(
  wh text
);

INSERT INTO test (wh ) VALUES ('---
Fri:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
- ''14''
- ''15''
- ''16''
- ''17''
Mon:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
- ''14''
- ''15''
- ''16''
- ''17''
Sat:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
Thu:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
- ''14''
- ''15''
- ''16''
- ''17''
Tue:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
- ''14''
- ''15''
- ''16''
- ''17''
Wed:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
- ''14''
- ''15''
- ''16''
- ''17''');

Code sample to create the destination table:

CREATE TABLE working_hours
(
  dow integer NOT NULL,
  hour_start time ,
  hour_end time,
  CONSTRAINT pk_dow_working_hours PRIMARY KEY (dow)
);

INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (1, '08:00:00', '18:00:00');
INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (2, '08:00:00', '18:00:00');
INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (3, '08:00:00', '18:00:00');
INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (4, '08:00:00', '18:00:00');
INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (5, '08:00:00', '18:00:00');
INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (6, '08:00:00', '14:00:00');

Could you provide the real table schema?

D-Shih
– D-Shih

2022-02-04 15:46:12 +00:00
Commented Feb 4, 2022 at 15:46 — D-Shih
– D-Shih, Commented Feb 4, 2022 at 15:46
Add sample datas at the same time

Philippe
– Philippe

2022-02-04 15:55:20 +00:00
Commented Feb 4, 2022 at 15:55 — Philippe
– Philippe, Commented Feb 4, 2022 at 15:55
How can I provide you a sample schema? Do you need the DDL?

luandrea
– luandrea

2022-02-04 16:59:38 +00:00
Commented Feb 4, 2022 at 16:59 — luandrea
– luandrea, Commented Feb 4, 2022 at 16:59

LukStorms · Accepted Answer · 2022-02-04 23:32:13Z

1

With a bit of regex magic the text can be transformed into a json with arrays.

Then it's just a matter of processing the json.

select 
  case l2.WeekDay
  when 'Mon' then 1
  when 'Tue' then 2
  when 'Wed' then 3
  when 'Thu' then 4
  when 'Fri' then 5
  when 'Sat' then 6
  when 'Sun' then 7
  end as DayOfWeek
, (l2.HourStart||':00')::time as TimeStart
, (l2.HourEnd||':00')::time as TimeEnd
from test
cross join lateral (
  select ('['||
   regexp_replace(
    regexp_replace(
     regexp_replace(wh, 
      '[^\w:]*([\w:]+)[^\w:-]*','"\1",','g')
    , '"(\w+):",', '{"day":"\1","hours":[','g')
    , '"(,[{])|",$', '"]}\1','g')
    ||']')::json as whjs
) l1
cross join lateral (
  select 
    value->>'day' as WeekDay
  , value->'hours'->>0  as HourStart
  , value->'hours'->>-1  as HourEnd
  from json_array_elements(l1.whjs)
) l2

dayofweek	timestart	timeend
5	08:00:00	17:00:00
1	08:00:00	17:00:00
6	08:00:00	13:00:00
4	08:00:00	17:00:00
2	08:00:00	17:00:00
3	08:00:00	17:00:00

Test on db<>fiddle here

answered Feb 4, 2022 at 23:32

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

luandrea Over a year ago

A thousand thanks LukStorms, you are a true Postgres and Regex black belt!

LukStorms Over a year ago

Thx, and glad that it works. But not many times I've seen a question where they deliver the data is such a weird format.

luandrea Over a year ago

It's from the database of OTRS, a service desk solution. They have a table names sysconfig_modified for generic configuration end they store them in a weird format.

LukStorms Over a year ago

Ah, well at least it's a generated format then, which makes it predictable.

luandrea Over a year ago

I'm still studing what you have done, because I need to do something similar with other configurations, this time for vacation days configuration.

|

Collectives™ on Stack Overflow

Parse String in Postgres

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related