Regex in postgres to extract datetime from text column-CodePudding

I have a column that has multiple comments along with some DateTime stamps. The example is like this:

[email protected] - 03/03/2022 13:04:40
Documents Pending
Some random comment

I want to extract only the DateTime stamp from this column. I tried using to_char, and to_date functions in PostgreSQL, but none of it seems to work for me. I also tried writing a regex to extract the DateTime stamp, but it didn't work.

What is the correct way to extract only DateTime from the above column? What would be the regular expression to extract the DateTime? Thanks in advance.

Edit: I want to extract a date from a column like this:

[email protected] - 12-Aug-2022
Documents Pending
Some random comment

How we can identify the month number or a date format from this comment?

CodePudding user response：

You could use SUBSTRING() here:

SELECT col,
       SUBSTRING(col FROM '\y\d{2}/\d{2}/\d{4} \d{2}:\d{2}:\d{2}\y') AS ts
FROM yourTable;

CodePudding user response：

This expression will extract the timestamp in ISO 8601 format.

select regexp_replace(
  col, 
  '.*\m(\d\d)\/(\d\d)\/(\d{4}) (\d\d:\d\d:\d\d)\M.*',
  '\3-\2-\1T\4'
)
from the_table;

Example:

select regexp_replace(
'[email protected] - 03/03/2022 13:04:40
Documents Pending
Some random comment', 
'.*\m(\d\d)\/(\d\d)\/(\d{4}) (\d\d:\d\d:\d\d)\M.*',
'\3-\2-\1T\4'
);

-- result 2022-03-03T13:04:40