Postgres中拼接日期与整数时分生成时间戳用于Grafana过滤

核心思路 #

要将timestamp_column的日期部分与start_time/end_time对应的时分拼接，需要完成两个关键步骤：

• 把整数类型的时分值（如100）转换为标准的HH:MI:SS格式时间字符串
• 提取timestamp_column的纯日期部分，与转换后的时分字符串组合成完整的timestamp类型值

具体SQL实现 #

方案一：字符串拼接方式

通过字符串格式化和拼接实现，逻辑直观易懂：

SELECT
  id,
  timestamp_column,
  start_time,
  end_time,
  -- 生成new_timestamp_start
  (DATE(timestamp_column) || ' ' || 
   substring(lpad(start_time::text, 4, '0'), 1, 2) || ':' || 
   substring(lpad(start_time::text, 4, '0'), 3, 2) || ':00')::timestamp AS new_timestamp_start,
  -- 生成new_timestamp_end
  (DATE(timestamp_column) || ' ' || 
   substring(lpad(end_time::text, 4, '0'), 1, 2) || ':' || 
   substring(lpad(end_time::text, 4, '0'), 3, 2) || ':00')::timestamp AS new_timestamp_end
FROM your_table_name;

• lpad(start_time::text, 4, '0')：将整数转成4位字符串（如100→'0100'，59→'0059'）
• substring()：拆分出小时和分钟部分，组合成HH:MI:SS格式
• DATE(timestamp_column)：提取原时间戳的纯日期部分，再与时分字符串拼接后转成timestamp类型

方案二：数值计算优化方式

用PostgreSQL内置的make_time()函数，通过数值运算直接生成时间部分，效率更高：

SELECT
  id,
  timestamp_column,
  start_time,
  end_time,
  -- 生成new_timestamp_start
  (DATE(timestamp_column) + make_time(
    (start_time / 100)::int,  -- 提取小时（如100/100=1）
    (start_time % 100)::int,  -- 提取分钟（如100%100=0）
    0  -- 秒数固定为0
  )) AS new_timestamp_start,
  -- 生成new_timestamp_end
  (DATE(timestamp_column) + make_time(
    (end_time / 100)::int,
    (end_time % 100)::int,
    0
  )) AS new_timestamp_end
FROM your_table_name;

这种方式避免了字符串操作，性能更优，也更易维护，推荐使用。

Grafana适配说明 #

将上述查询作为数据源的基础查询后，即可在Grafana面板中选择new_timestamp_start或new_timestamp_end作为时间字段，配合仪表盘的日期过滤器使用。

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