记一次 Python 项目全量替换至 UTC 时区的重构经历（下）

2023-11-30 10:27:50 浏览数 (2)

0x00.TL;DR

  • MariaDB：default-time_zone = ' 0:00'
  • CLickhouse：<timezone>UTC</timezone>
  • Elasticsearch：默认 UTC 且不支持修改

0x01.前言

代码层面的改动都改完之后，发现数据库也得做对应的修改

0x02.MariaDB

项目中有如下代码，created_on 是创建时取本地时间，updated_on 是创建 & 更新是取本地时间

代码语言：javascript复制
from sqlalchemy import Column, String, TIMESTAMP, text

class HasTime(object):
    created_on = Column(TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP'))
    updated_on = Column(TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

查阅 MariaDB 文档，CURRENT_TIMESTAMP & CURRENT_TIMESTAMP() 就是 NOW()

而我们需要的是 UTC 时间，需要用 UTC_TIMESTAMP

代码语言：javascript复制
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 7796821
Server version: 10.3.34-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> SELECT NOW(), UTC_TIMESTAMP();
 --------------------- --------------------- 
| NOW()               | UTC_TIMESTAMP()     |
 --------------------- --------------------- 
| 2023-11-29 20:33:31 | 2023-11-29 12:33:31 |
 --------------------- --------------------- 
1 row in set (0.000 sec)

也就是替换成如下代码

代码语言：javascript复制
from sqlalchemy import Column, String,TIMESTAMP, text

class HasTime(object):
    created_on = Column(TIMESTAMP, nullable=False, server_default=text('UTC_TIMESTAMP'))
    updated_on = Column(TIMESTAMP, nullable=False, server_default=text('UTC_TIMESTAMP ON UPDATE UTC_TIMESTAMP'))

但是在建表时却报错了，原因是 UTC_TIMESTAMP 不支持 ON UPDATE，只支持创建时取值

相同的问题早有提出，https://jira.mariadb.org/browse/MDEV-23715

查了一下并不是 bug 而是 feature，#103228

因此，就只能修改 MariaDB 的时区配置了，VM 上的时区是 CST，并且也有看到文章说用 SYSTEM 时会导致全局锁问题

配置方法很简单，修改 my.cnf，追加一行 default-time_zone = ' 0:00' 即可

0x03.Clickhouse

项目中有如下代码

代码语言：python代码运行次数：0复制
events_sql = f"SELECT toUnixTimestamp(toDateTime(time)) as time, sumMerge(count) as count, " 
             f"sumMerge(advertise) as advertise, sumMerge(update) as update, " 
             f"sumMerge(withdraw) as withdraw " 
             f"FROM {self.db}.{table} {filters} GROUP BY time ORDER BY time ASC " 
             f"{fill_sql};"
events = ck_conn.execute(events_sql)

其中 time 字段做了 2 次转换：toUnixTimestamp(toDateTime(time))

代码语言：javascript复制
ClickHouse client version 23.7.4.5 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 23.7.4 revision 54465.

Warnings:
 * Linux transparent hugepages are set to "always". Check /sys/kernel/mm/transparent_hugepage/enabled
 * Available disk space for data at server startup is too low (1GiB): /var/lib/clickhouse/
 * Available disk space for logs at server startup is too low (1GiB): /var/log/clickhouse-server
 * Obsolete settings ['background_fetches_pool_size', 'background_pool_size'] are changed. Please check 'select * from system.settings where changed and is_obsolete' and read the changelog.

golden-image :) SELECT toDateTime('2023-11-29 12:33:31') AS date_time;

SELECT toDateTime('2023-11-29 12:33:31') AS date_time

Query id: efc258e6-ded0-443f-8cfe-fcada3675f1e

┌───────────date_time─┐
│ 2023-11-29 12:33:31 │
└─────────────────────┘

1 row in set. Elapsed: 0.001 sec. 

golden-image :) SELECT toUnixTimestamp(toDateTime('2023-11-29 12:33:31')) AS date_time;

SELECT toUnixTimestamp(toDateTime('2023-11-29 12:33:31')) AS date_time

Query id: 0e85714c-2267-4c3b-a652-7f50687119bb

┌──date_time─┐
│ 1701232411 │
└────────────┘

1 row in set. Elapsed: 0.001 sec.

其中 toDateTime 会转换至本地时间，最终导致 toUnixTimestamp 的时间戳提前了 8h，不正确

可以追加 timezone 参数指定时区

代码语言：javascript复制
golden-image :) SELECT
    toDateTime('2023-11-29 12:33:31') AS time,
    toDateTime('2023-11-29 12:33:31') AS date_local,
    toDateTime('2023-11-29 12:33:31', 'Asia/Shanghai') AS date_cn,
    toDateTime('2023-11-29 12:33:31', 'UTC') AS date_utc,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31')) AS ts_local,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31', 'Asia/Shanghai')) AS ts_cn,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31', 'UTC')) AS ts_utc,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31'), 'UTC') AS ts_local_utc,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31', 'Asia/Shanghai'), 'UTC') AS ts_cn_utc,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31', 'UTC'), 'UTC') AS ts_utc_utc

SELECT
    toDateTime('2023-11-29 12:33:31') AS time,
    toDateTime('2023-11-29 12:33:31') AS date_local,
    toDateTime('2023-11-29 12:33:31', 'Asia/Shanghai') AS date_cn,
    toDateTime('2023-11-29 12:33:31', 'UTC') AS date_utc,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31')) AS ts_local,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31', 'Asia/Shanghai')) AS ts_cn,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31', 'UTC')) AS ts_utc,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31'), 'UTC') AS ts_local_utc,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31', 'Asia/Shanghai'), 'UTC') AS ts_cn_utc,
    toUnixTimestamp(toDateTime('2023-11-29 12:33:31', 'UTC'), 'UTC') AS ts_utc_utc

Query id: a9b4651a-1b79-4c8b-9379-c2ae0a79b651

┌────────────────time─┬──────────date_local─┬─────────────date_cn─┬────────────date_utc─┬───ts_local─┬──────ts_cn─┬─────ts_utc─┬─ts_local_utc─┬──ts_cn_utc─┬─ts_utc_utc─┐
│ 2023-11-29 12:33:31 │ 2023-11-29 12:33:31 │ 2023-11-29 12:33:31 │ 2023-11-29 12:33:31 │ 1701232411 │ 1701232411 │ 1701261211 │   1701232411 │ 1701232411 │ 1701261211 │
└─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┴────────────┴────────────┴────────────┴──────────────┴────────────┴────────────┘

1 row in set. Elapsed: 0.002 sec.

P.S. toUnixTimestamp 和 toDateTime 一样，都有第 2 个可选参数：timezone

其中 ts_utc & ts_utc_utc 的结果是想要的，也就是说 toDateTime 方法需要指定时区

如果不追加 timezone 指定时区的话，可以修改 ck 的配置，/etc/clickhouse-server/config.xml

取消注释：<timezone>UTC</timezone> 即可

0x04.Elasticsearch

参照：https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html#datehistogram-aggregation-time-zone

默认 UTC 时区且不支持修改

0x05. 后记

经过代码上的重构和数据库设置的同步修改，最终实现在项目的前后端统一使用 UTC

我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！

DAY 3/3，发文满 3 天成就达成

python utc 重构 2023腾讯·技术创作特训营第三期远哥制造

0 人点赞