记一次解决数据库时区问题

背景

最近在开发“上应小风筝”的电费统计功能后端接口。前端突然反馈说数据有问题：前端页面中计算得到的平均值和电费排名中的消费量不一致。（如图，图上 0.05 即为均值，但此图为事后截，无太大问题）

去查看后端返回的数据，发现最后 8 小时电费情况始终为 0 元，而与实际金额不符。因此怀疑是时区问题的锅。

Bug 的产生原因

虽然 Bug 原因是后来才找到的，但是这段要放在前面 😄

查看按日统计电费消费情况对应的 SQL 语句：

SELECT
	h.hour AS time,  -- 记录的日期和时间
	COALESCE(records.charged_amount, 0.00) AS chargement,  -- 充值金额
	ABS(COALESCE(records.used_amount, 0.00)) AS consumption  -- 消费金额
FROM (
	SELECT 
    	to_char(hour_range, 'yyyy-MM-dd HH24:00') AS hour
	FROM 
    	-- 从开始时间到结束时间生成逐小时的时间序列
    	generate_series(
            current_timestamp - '1 day'::interval,  -- 开始时间
            current_timestamp, '1 hour') AS hour_range  -- 结束时间
		) h
LEFT JOIN (
    -- 查询并聚合消费记录
	SELECT * 
    FROM dormitory.get_consumption_report_by_hour(
		current_timestamp - '1 day'::interval, 
		current_timestamp,
        room_id)
) AS records
ON h.hour = records.hour;

这段查询中主要有两张表，一是使用 generate_series 生成的连续的时间序列，一是聚合各寝室每小时电费耗电量产生的消费 / 充值情况表。之所以要这样做，是因为当某几次该寝室无消费时，数据库中不会存储该寝室电费的变动情况，这在将数据返回前端时可能出现缺失（如11时未消费，接口只返回9、11时的消费和充值数据），不太好绘图。通过 LEFT JOIN 查询，两张表就连接起来了。COALESCE 也是个很方便的函数，可以将 NULL 转成 0.00。

经过测试，问题主要出现在 to_char 函数上。由于会话中的时区是 UTC，所以数据库在转换时区时会将时间转换成 UTC 时间的字符串。如：

1
2
3

SET TIME ZONE 'UTC';

SELECT to_char('2021-1-1 8:00+8'::timestamptz, 'HH24:00');

查询会输出 00:00。这样，返回的结果自然就不对了。

解决过程

匆匆检索

在网上搜索时区问题，结果多是修改 postgresql.conf 文件中的 timezone 字段。那么就先看一下：

root@Aliyun:~# more /etc/postgresql/13/main/postgresql.conf | grep timezone
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
                                        # share/timezonesets/.

发现时区已经是东八区（上海）了，按理说。继续搜索，有人提到了在 SQL 语句中进行会话级别的时区设置，方法如下：

-- 显示时区
SHOW TIME ZONE;

-- 设置时区，也有说可以用 PRC 代表中国时区的。
SET TIME ZONE 'Asia/Shanghai';
-- 或
SET TIMEZONE TO 'Asia/Shanghai';

其实上述问题，在 DataGrip 的 Console 中编写和测试 SQL 时并未发现，可能是之前在 DataGrip 中设置使用了东八区时间。

经过测试上述 SQL 发现有希望解决，一些人的解决方案是，和 DataGrip 类似，由于 JDBC 的连接字符串中支持指定时区，Golang 的 PostgreSQL 驱动也支持，可对我却意味着陷入了一个新的问题——我使用的是 Rust 的 sqlx 库，不支持连接字符串中设置时区。

从 Rust 的 Sqlx 库文档到其依赖的 libpq 中要求的 Connection String，都没有指定时区的选项。在 PostgreSQL 数据库文档中的 Connection Settings 一节，也没提这事儿。

我都准备去 Github 上提 issue 了。

转瞬即逝的方法

有一个方法似乎可行，在程序刚开始时设置一下时区。经过尝试，结果正确。正当我打算跟前端交差时，就这么点了次查询，结果又错了。之后仿佛时而有问题，时而没问题。

1
2
3

sqlx::query("SET TIME ZONE 'Asia/Shanghai';")
	.execute(&pool)
	.await?;

后来发现是连接池的问题。连接池会创建多个连接，我也没有办法指定连接做查询。

——我总不能在每个查询前，SET 一下 TIME ZONE 吧？

——也不是不行，试了一下，第一个请求的时区仍然是错误的，但是后续请求正常。这意味着就算不考虑性能，程序也会存在不稳定的情况。当查询并发多、数据库扩展连接时，就会出现错误。另外，这样的写法心里别扭。

再探数据库时区

进展到这，我又改了改系统时区，发现没什么用。也尝试在其他 Linux 机器上使用 psql 连接我的服务器，尝试显示默认时区，却出乎意料地返回：

kite=# SHOW TIME ZONE;
 TimeZone
----------
 <+08>-08
(1 row)

我的程序却总是 UTC、UTC 的。莫不是 Rust 版pg 的数据库驱动偷工减料，默认拿 UTC 代替——应该不会，而且 DataGrip 不指定时区时默认也是 UTC 时区。

我又去看了看 pg 的客户端连接参数文档，明明白白地写着：

TimeZone (string)

Sets the time zone for displaying and interpreting time stamps. The built-in default is GMT, but that is typically overridden in postgresql.conf; initdb will install a setting there corresponding to its system environment. See Section 8.5.3 for more information.

算了不管了。进一步寻找资料，发现 StackOverflow 上有个用户的提问与此相关，回答说可以对用户组修改默认时区。不妨再试一试：

1	ALTER ROLE postgres SET TIMEZONE = '+8';

其中有段话：

Every connection initiated with this role will operate in its preset timezone automatically (unless instructed otherwise).

嗯…可是我试了还不行。可能真的是客户端强制默认使用 UTC 时区了，但我没有证据。不过学到了 PostgreSQL 中时区设置的各种方法。

最后的解决方案

说到底，解决这个问题共有两类方案，一是通过修改 SQL，使我可以手动指定所有时区，二是按前文所述，修改数据库连接的时区。

我也考虑过方案一，如一些资料中说，对时间戳强制加时区，如 current_timestamp::timestamptz AT TIME ZONE 'Asia/Shanghai'，但是在我的情况下不适合。

今天上午正好是以为主攻数据库的老师上课，和他讨论了好一会儿这个问题，无论是从可维护性还是从修改成本的角度，都应该选择方案二。

问题的解决方案很简单，sqlx 中有一个 after_connect 函数：

1	pub fn after_connect<F>(self, callback: F) -> PoolOptions<DB>

我只需要将数据库连接写成：

// Create database pool.
let pool = PgPoolOptions::new()
    .max_connections(10)
    .after_connect(|conn| {
        Box::pin(async move {
            conn.execute("SET TIME ZONE 'Asia/Shanghai';").await?;
            Ok(())
        })
    })
    .connect(&CONFIG.server.db.as_ref())
    .await
    .expect("Could not create database pool");

就好了。

后记

忙活了一天多，歇下来的时候，写了这篇文章。

也没有什么吧，搞技术的常态。

希望以后遇到困难，多看看文档，静下来思考一下，说不定呢？

主要参考资料

[1] 逝水-无痕, PostgreSQL时区、时间不一致、差8小时, CSDN

[2] Set timezone of PostgreSQL instance, StackOverflow.com