database - 使用 EXTRACT 获取单位时出现 PostgreSQL 错误

Question

我一直在尝试从指定的时间戳中提取月份:

SELECT EXTRACT(MONTH FROM '2019-01-02 00:00:00+00');

但是我得到以下错误:

ERROR: function pg_catalog.date_part(unknown, unknown) is not unique LINE 1: SELECT EXTRACT(MONTH FROM '2019-01-02 00:00:00+00'); ^ HINT: Could not choose a best candidate function. You might need to add explicit type casts. SQL state: 42725 Character: 8

但是，当我使用指定 CURRENT_TIMESTAMP 变量的相同命令时，会返回预期的结果。

SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP);

如何从我第一次尝试返回预期结果时获取单位？

Answer 1

将字符串转换为时间戳:

SELECT EXTRACT(MONTH FROM TIMESTAMP '2019-01-02 00:00:00+00');

来自 the documentation 的片段:

EXTRACT(field FROM source)

(...) source must be a value expression of type timestamp, time, orinterval. (Expressions of type date are cast to timestamp and cantherefore be used as well.)