postgresql - 将文本转换为 UTC 时间戳

Question

我的表中有文本格式的时间戳。它的格式如下 2019-05-30T05:48:29.095705642+01:00。我想将其转换为 UTC 时间戳。

我正在考虑先将文本转换为时间戳，然后再将其转换为 UTC。我正在执行 SELECT TO_TIMESTAMP('2019-05-30T05:48:29.095705642+01:00', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')但它不起作用。我无法弄清楚。

Answer 1

对于毫秒 (.MS)，您只能取 3 位小数。如果您改用 .US，则可以输入 6 位数字。

但如果您不想在单独的操作中去除数字(并且您确定总是有 3 位以上)，您可以通过添加 9 模式来忽略它们 (US999) .结果与之前使用剥离它的结果相同。

documentation (table 9.26)

SELECT 
    TO_TIMESTAMP(
        '2019-05-30T05:48:29.095705642+01:00', 
        'YYYY-MM-DD"T"HH24:MI:SS.US999"Z"'
     )

---

但最简单的方法——如果你真的有这种日期格式——是直接转换成 timestamptz。 PostgreSQL 为您施展魔法，无需考虑小数位数:

SELECT '2019-05-30T05:48:29.095705642+01:00'::timestamptz

---

从文本转换为时间戳后，您可以使用 at time zone 'UTC' 切换时区:

SELECT '2019-05-30T05:48:29.095705642+01:00'::timestamptz at time zone 'UTC'

demo:db<>fiddle

---

如评论中所述:未正确考虑时区。 “Z” 仅忽略字母 Z。要解析时区，您需要编写 TZH:TZM。您应该使用 PostgreSQL 魔法的另一个原因。