SQL中CONCAT函数拼接22、23点小时值异常问题咨询

问题现象 #

编写SQL查询时CONCAT函数运行异常：当FECHA_INICIO字段的小时值为22、23时，CONCAT无法正常完成拼接，其余小时值场景下拼接逻辑可正常运行。

原查询业务逻辑 #

• 从TASK_MANAGER_表筛选任务数据：匹配指定SITIO、排除ZONA为'ENTREGA CERTIFICADA'和'ZONA5'、状态为'liberada'或'terminada'
• 对FECHA_INICIO、FECHA_TERMINO字段做减5小时的时区偏移转换
• 基于'America/Mexico_city'时区下的FECHA_TERMINO时间判定对应close_date
• 提取FECHA_INICIO的小时值，按分钟区间划分1-4的15分钟时段标识QUARTER
• 通过CONCAT拼接小时值与QUARTER生成HQ字段
• 按CODIGO分组统计已完成任务量，最终按FECHA_INICIO排序

原SQL代码 #

SELECT CODIGO
, ZONA
, FECHA_INICIO
, FECHA_TERMINO
, hour(FECHA_INICIO) AS HOUR
, Case when minute(fecha_inicio) < 15 THEN 1 
            when minute(fecha_inicio) < 30 THEN 2
            when minute(fecha_inicio) < 45 THEN 3
            when minute(fecha_inicio) < 60 THEN 4 END AS QUARTER
, CONCAT((hour(FECHA_INICIO)), 
"-" , 
(Case when minute(fecha_inicio) < 15 THEN 1 
            when minute(fecha_inicio) < 30 THEN 2
            when minute(fecha_inicio) < 45 THEN 3
            when minute(fecha_inicio) < 60 THEN 4 END)) AS HQ
, sum(case when ESTADO = 'terminada' then 1 else 0 end) as complete

from 
( SELECT 
CASE WHEN extract(hour from convert_tz(FECHA_TERMINO,@@session.time_zone,'America/Mexico_city'))<12 
    THEN date(convert_tz(FECHA_TERMINO,@@session.time_zone,'America/Mexico_city')) 
    ELSE Date_add(date(convert_tz(FECHA_TERMINO,@@session.time_zone,'America/Mexico_city')), interval 1 day) 
END AS close_date
, CODIGO
, ZONA
, DATE_SUB(FECHA_INICIO, INTERVAL 5 HOUR) AS FECHA_INICIO
, DATE_SUB(FECHA_TERMINO, INTERVAL 5 HOUR) AS FECHA_TERMINO
, ESTADO
from TASK_MANAGER_
where ( ESTADO = 'liberada' or ESTADO = 'terminada')
and SITIO = '{{Bodega}}'
and ZONA NOT IN ('ENTREGA CERTIFICADA', 'ZONA5')
) as aux_tab
where close_date in ('{{ Fecha }}')
Group by CODIGO
ORDER BY FECHA_INICIO

问题根因 #

1. 分组逻辑不规范：仅按CODIGO字段分组，SELECT列表中其余非聚合字段未加入分组规则。在MySQL默认开启ONLY_FULL_GROUP_BY的环境中，会随机返回分组内任意行的字段值，22、23点属于跨天边界时间，同分组内存在多时段数据时极易返回异常值。
2. CONCAT隐式转换风险：hour()函数、CASE判断返回值均为整数类型，未显式转换为字符串就传入CONCAT，部分MySQL版本处理两位整数与符号拼接时会出现类型解析错误。
3. 时区转换逻辑不一致：计算close_date时使用CONVERT_TZ按官方时区规则做转换，计算FECHA_INICIO、FECHA_TERMINO时直接硬减5小时，未考虑墨西哥城时区的夏令时偏移，22、23点属于夏令时切换常见边界时段，会导致时间计算错误，进一步让CASE分支返回NULL。而CONCAT只要任意入参为NULL就会直接返回NULL，表现为拼接失败。
4. CASE分支无兜底：15分钟时段判断的CASE语句未写ELSE分支，一旦出现预期外的分钟值（比如时间计算异常导致分钟值为60或NULL），会直接返回NULL触发CONCAT异常。

修复方案 #

• 统一时区转换逻辑，所有时间偏移均使用CONVERT_TZ完成，避免硬编码偏移量
• 简化15分钟时段计算逻辑，用CEIL函数替代多层CASE WHEN，增加ELSE兜底避免返回NULL
• 对传入CONCAT的数值做显式字符串转换
• 补全GROUP BY字段，符合SQL语法规范

修复后SQL代码 #

SELECT 
    CODIGO,
    ZONA,
    FECHA_INICIO,
    FECHA_TERMINO,
    HOUR(FECHA_INICIO) AS HOUR,
    CEIL(MINUTE(FECHA_INICIO)/15) AS QUARTER,
    CONCAT(
        CAST(HOUR(FECHA_INICIO) AS CHAR),
        '-',
        CAST(CEIL(MINUTE(FECHA_INICIO)/15) AS CHAR)
    ) AS HQ,
    SUM(CASE WHEN ESTADO = 'terminada' THEN 1 ELSE 0 END) AS complete
FROM (
    SELECT 
        CASE 
            WHEN EXTRACT(HOUR FROM CONVERT_TZ(FECHA_TERMINO,@@session.time_zone,'America/Mexico_City')) < 12 
            THEN DATE(CONVERT_TZ(FECHA_TERMINO,@@session.time_zone,'America/Mexico_City'))
            ELSE DATE_ADD(DATE(CONVERT_TZ(FECHA_TERMINO,@@session.time_zone,'America/Mexico_City')), INTERVAL 1 DAY)
        END AS close_date,
        CODIGO,
        ZONA,
        CONVERT_TZ(FECHA_INICIO,@@session.time_zone,'America/Mexico_City') AS FECHA_INICIO,
        CONVERT_TZ(FECHA_TERMINO,@@session.time_zone,'America/Mexico_City') AS FECHA_TERMINO,
        ESTADO
    FROM TASK_MANAGER_
    WHERE 
        ESTADO IN ('liberada','terminada')
        AND SITIO = '{{Bodega}}'
        AND ZONA NOT IN ('ENTREGA CERTIFICADA', 'ZONA5')
) AS aux_tab
WHERE close_date = '{{ Fecha }}'
GROUP BY CODIGO, ZONA, FECHA_INICIO, FECHA_TERMINO, HOUR, QUARTER, HQ
ORDER BY FECHA_INICIO

注：如果业务确实需要仅按CODIGO聚合，需要明确其余字段的聚合规则（比如取MIN/MAX时间，而非直接查询裸字段），避免随机取值导致的计算异常。

内容的提问来源于stack exchange，提问作者Humberto Franco Felix