PostgreSQL从文本列提取DateTime的方法、正则及格式识别

1. 提取DateTime的方法及正则表达式 #

首先用正则定位文本中第一行的时间部分——两种格式的时间都位于邮箱后的-分隔符之后，直到该行末尾。

正则表达式 #

用正向预查匹配分隔符后的内容：

(?<=- ).+$

在PostgreSQL中，通过以下方式提取时间字符串：

-- 提取为数组，取第一个元素得到纯时间文本
SELECT (regexp_match(comment_column, '(?<=- ).+$'))[1] AS time_str FROM your_table;

转换为Timestamp类型 #

提取到时间字符串后，用to_timestamp结合coalesce兼容两种格式：

SELECT 
  coalesce(
    -- 处理带时分秒的格式
    to_timestamp((regexp_match(comment_column, '(?<=- ).+$'))[1], 'MM/DD/YYYY HH24:MI:SS'),
    -- 处理仅日期的格式，转换后时分秒默认补0
    to_timestamp((regexp_match(comment_column, '(?<=- ).+$'))[1], 'DD-Mon-YYYY')
  ) AS extracted_timestamp
FROM your_table;

2. 识别第二种格式的月份数字与日期格式 #

第二种格式为DD-Mon-YYYY（如12-Aug-2022），其中Mon是3位英文月份缩写（PostgreSQL默认支持英文缩写，若数据库语言非英文需调整lc_time参数）。

转换与提取月份数字 #

• 直接转换为timestamp后提取月份：

SELECT 
  extract(month FROM to_timestamp((regexp_match(comment_column, '(?<=- ).+$'))[1], 'DD-Mon-YYYY')) AS month_num
FROM your_table
-- 先筛选出第二种格式的行
WHERE comment_column ~ '\d{2}-[A-Za-z]{3}-\d{4}';

• 或用正则提取月份缩写后映射为数字：

SELECT 
  CASE (regexp_match(comment_column, '(?<=- \d{2}-)[A-Za-z]{3}(?=-\d{4})'))[1]
    WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3
    WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6
    WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8 WHEN 'Sep' THEN 9
    WHEN 'Oct' THEN 10 WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12
  END AS month_num
FROM your_table
WHERE comment_column ~ '\d{2}-[A-Za-z]{3}-\d{4}';

内容的提问来源于stack exchange，提问作者Suraj221b