Parse Postgres TIMESTAMPs from JSON blobs

text_to_timestamp.sql

CREATE FUNCTION text_to_timestamp(text) RETURNS TIMESTAMP
LANGUAGE sql IMMUTABLE AS
$$
  SELECT CASE
  WHEN $1 ~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(\.\d+)?$' THEN
    CAST($1 AS timestamp without time zone)
  END
$$;

CREATE TABLE t (
 id SERIAL NOT NULL, 
 title VARCHAR(255) generated always as (item ->> 'title') stored, 
 start_date TIMESTAMP generated always as (text_to_timestamp(item ->> 'startDate')) stored,
 item JSONB NOT NULL,
 PRIMARY KEY(id)
)

thanks a lot for these insights! i will add a thankyou in my talk at Symfony Con