PostgreSQL生成JSON时时间戳显示格式不一致问题及解决

问题场景 #

我编写了如下PostgreSQL SQL函数：

CREATE OR REPLACE FUNCTION aggregate_view(ticker text, from_time timestamp)
RETURNS TABLE (data text)
LANGUAGE sql AS
$func$
SELECT
    json_build_object(
        'candles',
        (SELECT json_agg(array [EXTRACT(EPOCH FROM ts_bucket), "open", high, low, "close", volume :: BIGINT]) AS json_array_of_arrays
        FROM exchange.candles_d1
        WHERE exchange.candles_d1.ticker = $1 AND candles_d1.ts_bucket >= $2 AND candles_d1.ts_bucket < $2 + INTERVAL '1 hour'),

        'kvwap',
        (SELECT json_agg(array [EXTRACT(EPOCH FROM ts_bucket), m1, m5, m15, m30, h1, h2, h4, d1, low, vwap, high]) AS json_array_of_arrays
        FROM exchange.kvwap_d1
        WHERE exchange.kvwap_d1.ticker = $1 AND kvwap_d1.ts_bucket >= $2 AND kvwap_d1.ts_bucket < $2 + INTERVAL '1 hour'),

        'zones',
        (SELECT json_agg(array[EXTRACT(EPOCH FROM ts_confirmation)::BIGINT, EXTRACT(EPOCH FROM ts_end)::BIGINT, confirmations, CAST(is_continuation AS INT)])
        AS json_array_of_array
        FROM analysis.zones
        WHERE analysis.zones.ticker = $1 AND "interval" = 'D1' AND ts_confirmation >= $2 AND ts_confirmation < $2+ INTERVAL '1 hour')
    )
$func$;

执行后生成的JSON输出中，不同表对应的时间戳格式存在差异：

{
   "candles":[ 
      [
         1577836800,
         7189.43,
         7260.43,
         7170.15,
         7197.57,
         409678761
      ]
   ],
   "kvwap":[
      [
         1.5778368e+09,
         7213.8125,
         7213.5947,
         7212.4907,
         7210.914,
         7209.1133,
         7207.789,
         7207.2,
         7206.961,
         7170.15,
         7213.8574,
         7260.43
      ]
   ],
   "zones":null
}

可见两个时间戳为同一数值，但一个显示为整数1577836800，另一个显示为科学计数法1.5778368e+09。请问该差异产生的原因是什么？如何避免数字以科学计数法显示？

原因分析 #

• EXTRACT(EPOCH FROM ts_bucket) 默认返回double precision（浮点数）类型。在candles的数组中，后续的volume :: BIGINT是整数类型，PostgreSQL构建数组时会自动将整个数组的类型统一为bigint[]，浮点数被转换为整数，因此输出为整数格式。
• 而kvwap的数组中，后续的m1、m5等字段均为浮点/数值类型，整个数组的类型被统一为double precision[]，浮点数在JSON序列化时，当数值较大时会触发科学计数法格式，因此出现了1.5778368e+09的显示。

解决方法 #

方法1：显式转换时间戳为整数类型 #

在EXTRACT(EPOCH ...)后添加::BIGINT或::INTEGER，强制将浮点数转为整数类型，确保时间戳无论数组其他字段类型如何，都以整数格式输出：

-- 修改后的candles子查询部分
SELECT json_agg(array [EXTRACT(EPOCH FROM ts_bucket)::BIGINT, "open", high, low, "close", volume :: BIGINT]) AS json_array_of_arrays
FROM exchange.candles_d1
WHERE exchange.candles_d1.ticker = $1 AND candles_d1.ts_bucket >= $2 AND candles_d1.ts_bucket < $2 + INTERVAL '1 hour'

-- 修改后的kvwap子查询部分
SELECT json_agg(array [EXTRACT(EPOCH FROM ts_bucket)::BIGINT, m1, m5, m15, m30, h1, h2, h4, d1, low, vwap, high]) AS json_array_of_arrays
FROM exchange.kvwap_d1
WHERE exchange.kvwap_d1.ticker = $1 AND kvwap_d1.ts_bucket >= $2 AND kvwap_d1.ts_bucket < $2 + INTERVAL '1 hour'

方法2：使用json_build_array替代原生数组构造 #

原生array[]会自动统一数组元素类型，而json_build_array会保留每个元素的原始类型，同时可以精准控制时间戳的类型：

-- 修改后的candles子查询部分
SELECT json_agg(json_build_array(EXTRACT(EPOCH FROM ts_bucket)::BIGINT, "open", high, low, "close", volume :: BIGINT)) AS json_array_of_arrays
FROM exchange.candles_d1
WHERE exchange.candles_d1.ticker = $1 AND candles_d1.ts_bucket >= $2 AND candles_d1.ts_bucket < $2 + INTERVAL '1 hour'

-- 修改后的kvwap子查询部分
SELECT json_agg(json_build_array(EXTRACT(EPOCH FROM ts_bucket)::BIGINT, m1, m5, m15, m30, h1, h2, h4, d1, low, vwap, high)) AS json_array_of_arrays
FROM exchange.kvwap_d1
WHERE exchange.kvwap_d1.ticker = $1 AND kvwap_d1.ts_bucket >= $2 AND kvwap_d1.ts_bucket < $2 + INTERVAL '1 hour'

修改后的效果 #

两种方法都能让时间戳以整数格式输出，示例输出如下：

{
   "candles":[ 
      [
         1577836800,
         7189.43,
         7260.43,
         7170.15,
         7197.57,
         409678761
      ]
   ],
   "kvwap":[
      [
         1577836800,
         7213.8125,
         7213.5947,
         7212.4907,
         7210.914,
         7209.1133,
         7207.789,
         7207.2,
         7206.961,
         7170.15,
         7213.8574,
         7260.43
      ]
   ],
   "zones":null
}

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