MariaDB如何将文本中的日期转换为可用于WHERE查询的标准日期？

Got it, let's work through this problem together. You need to pull that date-time string out of the Entity.name field, turn it into a proper date type, and then use it to filter records in your WHERE clause. Here's how to do it across common database systems, plus some tips to avoid headaches:

MySQL Solution #

First, we'll use REGEXP_SUBSTR to grab the date-time inside the parentheses, then STR_TO_DATE to convert it to a standard datetime type. The format string '%d.%m.%Y %H:%i' matches your dd.mm.yyyy HH:mm pattern.

SELECT 
    Entity.name, 
    Entity.id, 
    Entity.created, 
    ComplDate.field_completion_date_value,
    -- Extract and convert the date from Entity.name
    STR_TO_DATE(REGEXP_SUBSTR(Entity.name, '\\((.*?)\\)', 1, 1, NULL, 1), '%d.%m.%Y %H:%i') AS extracted_date
FROM 
    application_form_entity AS Entity 
LEFT JOIN 
    application_form_entity__field_completion_date AS ComplDate 
        ON ComplDate.entity_id = Entity.id
-- Filter using the converted date
WHERE 
    STR_TO_DATE(REGEXP_SUBSTR(Entity.name, '\\((.*?)\\)', 1, 1, NULL, 1), '%d.%m.%Y %H:%i') 
    BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59';

Edge Case Handling #

If some Entity.name entries don't follow the pattern (no date in parentheses), STR_TO_DATE will return NULL. Use IFNULL to set a fallback value if needed:

IFNULL(STR_TO_DATE(REGEXP_SUBSTR(Entity.name, '\\((.*?)\\)', 1, 1, NULL, 1), '%d.%m.%Y %H:%i'), '1900-01-01 00:00:00') AS extracted_date

PostgreSQL Solution #

PostgreSQL uses regexp_match to extract the substring, then to_timestamp for conversion. The format specifier 'DD.MM.YYYY HH24:MI' matches your date pattern.

SELECT 
    Entity.name, 
    Entity.id, 
    Entity.created, 
    ComplDate.field_completion_date_value,
    -- Extract and convert the date
    to_timestamp((regexp_match(Entity.name, '\\((.*?)\\)'))[1], 'DD.MM.YYYY HH24:MI') AS extracted_date
FROM 
    application_form_entity AS Entity 
LEFT JOIN 
    application_form_entity__field_completion_date AS ComplDate 
        ON ComplDate.entity_id = Entity.id
-- Filter with the converted date
WHERE 
    to_timestamp((regexp_match(Entity.name, '\\((.*?)\\)'))[1], 'DD.MM.YYYY HH24:MI') 
    BETWEEN '2018-01-01' AND '2019-01-01';

Performance Tip #

If you run this query often, converting the date on the fly can slow things down. Consider adding a computed column (or a regular column you update periodically) to store the converted date, then add an index on it. This will make your WHERE clause filters much faster.

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