将VARCHAR类型时间列转换为TIME类型并用于WHERE子句查询

一、转换规则解析 #

先明确核心转换逻辑：

• 原格式Time: X'000'中的X是关键数字，拆分规则：
  • X的前n-1位为小时数（n为X的长度）
  • X的最后1位乘以10为分钟数
  • 秒数固定为00
• 对应示例：
  • Time: 70'000 → X=70 → 小时7，分钟0×10=0 → 07:00:00
  • Time: 143'000 → X=143 → 小时14，分钟3×10=30 →14:30:00

二、不同数据库的实现方案 #

1. MySQL #

（1）列转换逻辑

通过字符串函数提取数字并拼接成TIME格式：

SELECT 
  STR_TO_DATE(
    CONCAT(
      LPAD(LEFT(REGEXP_REPLACE(dtib, 'Time: (\\d+)\\'000', '$1'), LENGTH(REGEXP_REPLACE(dtib, 'Time: (\\d+)\\'000', '$1'))-1), 2, '0'),
      ':',
      LPAD(RIGHT(REGEXP_REPLACE(dtib, 'Time: (\\d+)\\'000', '$1'), 1)*10, 2, '0'),
      ':00'
    ), '%H:%i:%s'
  ) AS converted_time
FROM your_table;

（2）设置变量并筛选数据

-- 定义时间范围变量
SET @btibA = '07:00:00';
SET @btibB = '15:00:00';

-- 执行筛选查询
SELECT *
FROM your_table
WHERE STR_TO_DATE(
    CONCAT(
      LPAD(LEFT(REGEXP_REPLACE(dtib, 'Time: (\\d+)\\'000', '$1'), LENGTH(REGEXP_REPLACE(dtib, 'Time: (\\d+)\\'000', '$1'))-1), 2, '0'),
      ':',
      LPAD(RIGHT(REGEXP_REPLACE(dtib, 'Time: (\\d+)\\'000', '$1'), 1)*10, 2, '0'),
      ':00'
    ), '%H:%i:%s'
  ) BETWEEN @btibA AND @btibB;

2. SQL Server #

（1）列转换逻辑

SELECT 
  CAST(
    CONCAT(
      RIGHT('0' + LEFT(SUBSTRING(dtib, 7, LEN(dtib)-6), LEN(SUBSTRING(dtib, 7, LEN(dtib)-6))-1), 2),
      ':',
      RIGHT('0' + CAST(RIGHT(SUBSTRING(dtib, 7, LEN(dtib)-6), 1)*10 AS VARCHAR(2)), 2),
      ':00'
    ) AS TIME
  ) AS converted_time
FROM your_table;

说明：SUBSTRING(dtib,7,LEN(dtib)-6)用于提取Time: 与'000之间的数字部分。

（2）设置变量并筛选数据

-- 定义时间范围变量
DECLARE @btibA TIME = '07:00:00';
DECLARE @btibB TIME = '15:00:00';

-- 执行筛选查询
SELECT *
FROM your_table
WHERE CAST(
    CONCAT(
      RIGHT('0' + LEFT(SUBSTRING(dtib, 7, LEN(dtib)-6), LEN(SUBSTRING(dtib, 7, LEN(dtib)-6))-1), 2),
      ':',
      RIGHT('0' + CAST(RIGHT(SUBSTRING(dtib, 7, LEN(dtib)-6), 1)*10 AS VARCHAR(2)), 2),
      ':00'
    ) AS TIME
  ) BETWEEN @btibA AND @btibB;

3. Oracle #

（1）列转换逻辑

SELECT 
  TO_DATE(
    CONCAT(
      LPAD(SUBSTR(REGEXP_REPLACE(dtib, 'Time: (\d+)\'000', '\1'), 1, LENGTH(REGEXP_REPLACE(dtib, 'Time: (\d+)\'000', '\1'))-1), 2, '0'),
      ':',
      LPAD(TO_NUMBER(SUBSTR(REGEXP_REPLACE(dtib, 'Time: (\d+)\'000', '\1'), -1))*10, 2, '0'),
      ':00'
    ), 'HH24:MI:SS'
  ) AS converted_time
FROM your_table;

（2）设置变量并筛选数据

-- 定义时间范围变量（适用于SQL*Plus环境）
DEFINE btibA = '07:00:00';
DEFINE btibB = '15:00:00';

-- 执行筛选查询
SELECT *
FROM your_table
WHERE TO_DATE(
    CONCAT(
      LPAD(SUBSTR(REGEXP_REPLACE(dtib, 'Time: (\d+)\'000', '\1'), 1, LENGTH(REGEXP_REPLACE(dtib, 'Time: (\d+)\'000', '\1'))-1), 2, '0'),
      ':',
      LPAD(TO_NUMBER(SUBSTR(REGEXP_REPLACE(dtib, 'Time: (\d+)\'000', '\1'), -1))*10, 2, '0'),
      ':00'
    ), 'HH24:MI:SS'
  ) BETWEEN TO_DATE('&btibA', 'HH24:MI:SS') AND TO_DATE('&btibB', 'HH24:MI:SS');

三、优化建议 #

如果需要频繁使用该转换逻辑，可创建自定义函数封装转换过程，避免重复编写代码。以MySQL为例：

DELIMITER //
CREATE FUNCTION convert_dtib_to_time(dtib_str VARCHAR(20))
RETURNS TIME
DETERMINISTIC
BEGIN
  DECLARE num_str VARCHAR(10);
  SET num_str = REGEXP_REPLACE(dtib_str, 'Time: (\\d+)\\'000', '$1');
  RETURN STR_TO_DATE(
    CONCAT(
      LPAD(LEFT(num_str, LENGTH(num_str)-1), 2, '0'),
      ':',
      LPAD(RIGHT(num_str,1)*10, 2, '0'),
      ':00'
    ), '%H:%i:%s'
  );
END //
DELIMITER ;

调用方式简化为：

SELECT convert_dtib_to_time(dtib) FROM your_table;

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