DATE_FORMAT in Postgresql?

Question

我使用MySQL进行了此查询，我需要将其转换为PostgreSQL查询

SELECT
count(*) AS aggregate
FROM
"contracts"
WHERE
DATE_FORMAT(created_at, '%Y-%c') = '2022-1'

我得到了这个错误：

Query 1 ERROR: ERROR:  function date_format(timestamp without time zone, unknown) does not exist
LINE 6:  DATE_FORMAT(created_at, '%Y-%c') = '2022-1'

Answer 1

您可以使用TO_CHAR来格式化日期或时间戳。

SELECT count(*) AS aggregate
FROM contracts
WHERE TO_CHAR(created_at, 'yyyy-mm') = '2022-01'

或者使用可搜索的替代

SELECT count(*) AS aggregate
FROM contracts
WHERE created_at >= '2022-01-01' 
AND created_at <  '2022-02-01'