GCP BigQuery中拼接日期与小时转换为Timestamp的方法咨询

你的核心思路是可行的，但可以针对潜在问题和数据库特性做以下优化：

• 修复小时位数的兼容性问题：如果hr是个位数（比如5），直接拼接会得到02/12/2024 5:00:00，而格式串HH24要求两位小时数，可能导致转换失败。建议用LPAD(HR, 2, '0')补零，确保小时格式统一：

  TO_TIMESTAMP(CONCAT(DAY, ' ', LPAD(HR, 2, '0'), ':00:00'), 'MM/DD/YYYY HH24:MI:SS')
  

• 利用数据库原生函数替代字符串拼接：不同数据库有更高效的日期构造函数，避免字符串转换的开销：

  • PostgreSQL：用MAKE_TIMESTAMP直接构造，无需拼接字符串：

    SELECT
      MAKE_TIMESTAMP(
        EXTRACT(YEAR FROM TO_DATE(DAY, 'MM/DD/YYYY'))::INT,
        EXTRACT(MONTH FROM TO_DATE(DAY, 'MM/DD/YYYY'))::INT,
        EXTRACT(DAY FROM TO_DATE(DAY, 'MM/DD/YYYY'))::INT,
        HR::INT,
        0,
        0.0
      ) AS target_timestamp
    FROM tabledb.tablename
    

  • Oracle：通过日期加小时间隔实现：

    SELECT
      TO_TIMESTAMP(DAY, 'MM/DD/YYYY') + NUMTODSINTERVAL(HR, 'HOUR') AS target_timestamp
    FROM tabledb.tablename
    

• 添加异常数据处理：如果day格式不合法或hr超出0-23范围，转换会报错。可以用条件判断处理异常：

  SELECT
    CASE
      WHEN VALIDATE_CONVERSION(CONCAT(DAY, ' ', LPAD(HR, 2, '0'), ':00:00') AS TIMESTAMP FORMAT 'MM/DD/YYYY HH24:MI:SS') = 1
      THEN TO_TIMESTAMP(CONCAT(DAY, ' ', LPAD(HR, 2, '0'), ':00:00'), 'MM/DD/YYYY HH24:MI:SS')
      ELSE NULL -- 可替换为你需要的默认值
    END AS target_timestamp
  FROM tabledb.tablename
  

通用兼容版写法 #

如果需要适配多数数据库，推荐补零后的拼接写法：

SELECT
  TO_TIMESTAMP(CONCAT(DAY, ' ', LPAD(HR, 2, '0'), ':00:00'), 'MM/DD/YYYY HH24:MI:SS') AS target_timestamp
FROM tabledb.tablename

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