PostgreSQL timestamptz在C# ExecuteScalar中JSON格式异常问题

我有一个返回JSON的PostgreSQL函数，其中包含timestamptz类型字段：

• 在Datagrip控制台执行该函数时，返回的JSON中时间字段格式为：

  {..., "time_created":"2024-05-15T11:14:23.384266+00:00",...}
  

• 但在C#中用ExecuteScalar调用同一函数时，返回值的时间字段变成了：

  {..., "time_created":"2024-05-15T09:14:23.384266+02:00",...}
  

即使函数先将JSON转为text返回，结果仍一致。时间戳被转换为服务器时区并添加偏移量，无法直接在Quasar控件中渲染。

当函数返回包含timestamptz的表时，值会被正确序列化，生成如"time_created":"2024-05-15T11:14:23.384266Z"的格式，可正常在Quasar输入控件中使用。

请问：为何ExecuteScalar会对返回的text/json进行时区转换？如何让其生成带...Z后缀的格式？

函数示例 #

返回text类型的函数：

create function data_survey_r("Key" integer) returns text language plpgsql as $$ 
BEGIN
   RETURN (SELECT ROW_TO_JSON(t)::text FROM data.survey t WHERE t.id = "Key");
END $$;

返回json类型的函数：

create function data_survey_r("Key" integer) returns json language plpgsql as $$ 
BEGIN
   RETURN (SELECT ROW_TO_JSON(t) FROM data.survey t WHERE t.id = "Key");
END $$;

C#调用说明 #

使用ExecuteScalar调用上述函数时，返回的JSON时间字段出现时区偏移转换问题。

原因分析 #

1. PostgreSQL序列化规则：row_to_json处理timestamptz时，会根据当前会话的时区设置生成带偏移的时间字符串。Datagrip控制台的会话时区默认可能为UTC，而C#的Npgsql驱动默认会将PostgreSQL会话时区设为服务器所在时区，导致序列化后的时间偏移格式不同。
2. 返回类型差异：当函数返回表时，Npgsql驱动直接读取timestamptz的二进制原始值，在客户端按UTC（带Z后缀）格式序列化；但返回JSON/text时，驱动仅读取PostgreSQL已序列化好的字符串，不会二次转换，因此保留了会话时区下的偏移格式。

解决方案 #

方案一：在PostgreSQL函数中强制生成UTC+Z格式时间 #

手动将timestamptz字段格式化为UTC时间并添加Z后缀，彻底摆脱会话时区影响：

create function data_survey_r("Key" integer) returns json language plpgsql as $$ 
BEGIN
   RETURN (
      SELECT json_build_object(
         'id', t.id,
         'time_created', to_char(t.time_created AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'),
         -- 补充其他需要的字段
         ...
      )
      FROM data.survey t WHERE t.id = "Key"
   );
END $$;

如果不想逐个字段构建JSON，也可以用jsonb_set修改原始JSON的时间字段：

create function data_survey_r("Key" integer) returns json language plpgsql as $$ 
DECLARE
   original_json json;
BEGIN
   SELECT row_to_json(t) INTO original_json FROM data.survey t WHERE t.id = "Key";
   RETURN jsonb_set(
      original_json::jsonb,
      '{time_created}',
      to_json(to_char((original_json->>'time_created')::timestamptz AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'))
   )::json;
END $$;

方案二：修改C#连接的时区配置 #

在数据库连接字符串中指定会话时区为UTC，让row_to_json生成+00:00格式的时间，再在C#中替换为Z后缀：

// 连接字符串示例
var connectionString = "Host=myhost;Username=myuser;Password=mypass;Database=mydb;TimeZone=UTC";

处理返回结果：

var jsonResult = (string)await cmd.ExecuteScalarAsync();
var formattedJson = jsonResult.Replace("+00:00", "Z");

方案三：在C#中解析后重新序列化时间 #

使用Newtonsoft.Json解析JSON，将时间字段转换为UTC格式后重新序列化：

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

var jsonResult = (string)await cmd.ExecuteScalarAsync();
var jObj = JObject.Parse(jsonResult);
var timeCreated = DateTimeOffset.Parse(jObj["time_created"].ToString());
jObj["time_created"] = timeCreated.ToUniversalTime().ToString("yyyy-MM-dd'T'HH:mm:ss.fffffff'Z'");
var formattedJson = jObj.ToString(Formatting.None);

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