如何将当前日期时间转为Oracle SQL WHERE子句可用的特定格式变量

方法一：生成当前时间格式字符串，匹配字段前缀 #

直接将当前系统时间转换为目标YYYYMMDD.HH格式（单数小时无前置零），通过LIKE匹配foo_number字段的前缀部分。

核心是用TO_CHAR函数生成格式字符串：

• TO_CHAR(SYSDATE, 'YYYYMMDD')得到日期部分（如20240621）
• TO_CHAR(SYSDATE, 'FMHH24')得到24小时制的小时数，FM修饰符会自动去掉前置零（如9点返回9而非09）

示例SQL：

SELECT foo_number
FROM bar_log
WHERE foo_number LIKE TO_CHAR(SYSDATE, 'YYYYMMDD') || '.' || TO_CHAR(SYSDATE, 'FMHH24') || '%';

如果需要使用绑定变量（推荐，减少硬解析提升性能），可以在PL/SQL中预先生成变量：

DECLARE
    v_time_pattern VARCHAR2(20);
BEGIN
    v_time_pattern := TO_CHAR(SYSDATE, 'YYYYMMDD') || '.' || TO_CHAR(SYSDATE, 'FMHH24') || '%';
    -- 这里根据实际需求处理查询结果，比如批量输出或存入变量
    FOR rec IN (SELECT foo_number FROM bar_log WHERE foo_number LIKE v_time_pattern) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.foo_number);
    END LOOP;
END;
/

方法二：解析字段时间部分，匹配当前小时区间 #

如果需要更严谨的时间匹配（比如避免SQL执行时跨小时导致的匹配误差），可以反向截取foo_number的时间前缀，转换为日期类型后，判断是否落在当前小时的时间区间内。

示例SQL：

SELECT foo_number
FROM bar_log
WHERE 
    -- 截取字段中的时间部分并转为日期
    TO_DATE(SUBSTR(foo_number, 1, INSTR(foo_number, '^') - 1), 'YYYYMMDD.HH24')
    -- 匹配当前小时的完整时间范围（从当前小时0分0秒到59分59秒）
    BETWEEN TRUNC(SYSDATE, 'HH24')
    AND TRUNC(SYSDATE, 'HH24') + INTERVAL '59' MINUTE + INTERVAL '59' SECOND;

两种方法的优缺点对比 #

• 方法一：实现简单，无需日期解析，适合字段格式固定的场景；若foo_number存在基于前缀的函数索引（如CREATE INDEX idx_bar_log_foo_prefix ON bar_log(SUBSTR(foo_number,1,11))），查询性能会大幅提升。
• 方法二：时间匹配更精准，能处理跨小时的边界情况；但需要解析字符串为日期，若无对应函数索引，性能会略低于方法一。

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