Coding的痕迹

一位互联网奔跑者的网上日记

0%

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

背景

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

电费统计页(事后截图)

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

Bug 的产生原因

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 字段。那么就先看一下:

1
2
3
4
5
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 语句中进行会话级别的时区设置,方法如下:

1
2
3
4
5
6
7
-- 显示时区
SHOW TIME ZONE;

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

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

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 连接我的服务器,尝试显示默认时区,却出乎意料地返回:

1
2
3
4
5
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> 

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

1
2
3
4
5
6
7
8
9
10
11
12
// 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