SQL中将数字存储的时间转换为AM/PM格式时分秒的方法

业务库使用整数存储日内时段的规则为：直接拼接时、分、秒的十进制数值，省略分隔符与前导零，例：上午8点33分15秒存储为83315，下午2点07分09秒存储为140709。

核心转换逻辑 #

拆分与格式化分三步完成：

• 对整数值做左侧补零处理，统一为6位长度字符串，避免不足两位的时、分、秒段识别错位
• 按HHMMSS规则将补位后的字符串解析为数据库原生时间类型
• 调用数据库内置时间格式化函数，输出12小时制带AM/PM标识的hh:mm:ss AM/PM格式结果

各主流数据库可直接复用的实现代码 #

MySQL / MariaDB #

直接使用内置时间格式化函数即可，无需手动拆分分量：

-- 假设存储时段的字段为time_num，业务表为biz_record
SELECT
  TIME_FORMAT(LPAD(time_num, 6, '0'), '%h:%i:%s %p') AS format_time
FROM biz_record;

参数说明：LPAD(time_num,6,'0')会把不足6位的数值左侧补零，比如90302（9点03分02秒）会被补为090302，避免识别错误；%p对应AM/PM标识。
验证值83315转换结果为08:33:15 AM，143315转换结果为02:33:15 PM，符合预期。

PostgreSQL #

通过字符串补位+类型转换+格式化实现：

SELECT
  TO_CHAR(TO_DATE(LPAD(time_num::text, 6, '0'), 'HH24MISS'), 'HH12:MI:SS AM') AS format_time
FROM biz_record;

SQL Server #

2012及以上版本可使用TIMEFROMPARTS直接拼接时间分量，写法更简洁：

SELECT
  FORMAT(
    TIMEFROMPARTS(time_num/10000, (time_num/100)%100, time_num%100, 0, 0),
    'hh:mm:ss tt'
  ) AS format_time
FROM biz_record;

低版本可通过字符串插入分隔符的方式转换：

SELECT
  CONVERT(VARCHAR,
    STUFF(STUFF(RIGHT('000000'+CAST(time_num AS VARCHAR(6)),6),3,0,':'),6,0,':'),
    109
  ) AS format_time
FROM biz_record;

Oracle #

通过补位+日期解析+字符格式化实现：

SELECT
  TO_CHAR(TO_DATE(LPAD(time_num,6,'0'), 'HH24MISS'), 'HH:MI:SS AM') AS format_time
FROM biz_record

注意事项 #

• 必须做6位补零处理：比如凌晨1点02分03秒存储值为10203，不补零直接解析会被识别为10点20分03秒，出现严重偏差
• 建议提前做数据合法性校验：合法值的小时段范围为0-23，分钟、秒段范围为0-59，避免非法值导致转换报错
• 若数据库默认语言非英文，AM/PM可能输出本地语言结果，可通过设置会话时间区域参数固定输出英文标识

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