MySQL迁移PieCloudDB：DATE_FORMAT与PERIOD_ADD函数替代方案咨询

PieCloudDB兼容PostgreSQL语法，针对你用到的MySQL函数，可按以下方式替换：

• DATE_FORMAT(day, '%Y%m')：替换为PostgreSQL的to_char(day, 'YYYYMM')，能实现相同的YYYYMM格式日期字符串转换效果。
• PERIOD_ADD(yearmonth, 1)：原功能为给YYYYMM格式的年月值加1个月，可通过「字符串转日期→加月间隔→转回字符串」实现：to_char(to_date(yearmonth, 'YYYYMM') + interval '1 month', 'YYYYMM')

WITH base AS (
        SELECT account_id, to_char(day, 'YYYYMM') AS yearmonth
        FROM Transactions
            JOIN Accounts USING (account_id)
        WHERE type = 'Creditor'
        GROUP BY account_id, to_char(day, 'YYYYMM')
        HAVING SUM(amount) > MAX(max_income)
    )
SELECT DISTINCT account_id
FROM base
WHERE (account_id, to_char(to_date(yearmonth, 'YYYYMM') + interval '1 month', 'YYYYMM')) IN (
    SELECT account_id, yearmonth
    FROM base
)

补充：如果day字段是日期类型，也可以用date_trunc('month', day)获取当月起始日期后再转格式，写法为to_char(date_trunc('month', day), 'YYYYMM')，效果和前者一致。

内容的提问来源于stack exchange，提问作者Meliodas Dragon