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

原创

远哥制造

发布于 2023-11-30 10:27:50

5780

发布于 2023-11-30 10:27:50

文章被收录于专栏：远哥制造远哥制造

0x00.TL;DR

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

0x01.前言

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

0x02.MariaDB

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

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

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)

也就是替换成如下代码

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

项目中有如下代码

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))

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 参数指定时区

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