PostgreSQL如何将数值类型的PAYMENTDATE字段转换为时间格式

修正方案 #

你当前的TO_TIMESTAMP写法存在两个问题：

• 数值类型的PAYMENTDATE无法直接被TO_TIMESTAMP识别，需要先转成字符串类型
• 格式模板YYYYMMDD是TO_TIMESTAMP的入参，不能单独作为字段列在SELECT中

常用转换写法 #

根据你的需求选择对应写法：

• 转成timestamp类型：

TO_TIMESTAMP(PAYMENTDATE::VARCHAR, 'YYYYMMDD')

• 转成date类型（无时分秒，更适合纯日期场景）：

TO_DATE(PAYMENTDATE::VARCHAR, 'YYYYMMDD')

• 转成自定义格式的日期字符串（比如2021-08-01格式）：

TO_CHAR(TO_TIMESTAMP(PAYMENTDATE::VARCHAR, 'YYYYMMDD'), 'YYYY-MM-DD')

修正后的完整SQL #

这里以转成YYYY-MM-DD格式的字符串为例，你可以根据需要替换转换逻辑：

SELECT BANKCODE, PAYMENTDATE,
Current_Date,
-- 替换为正确的转换逻辑
TO_CHAR(TO_TIMESTAMP(PAYMENTDATE::VARCHAR, 'YYYYMMDD'), 'YYYY-MM-DD') AS FORMAT_PAYMENT_DATE,
PAYMENTDATE::VARCHAR,
SUM (CASE WHEN (TABLETYPE='ET') AND (TRANSACTIONTYPE=2) THEN 1 ELSE 0 END) "OGS",
SUM (CASE WHEN (TABLETYPE='ET') AND (TRANSACTIONTYPE=2) THEN APPROVEDPRICE ELSE 0 END)/100 "OGS GELIR ",
SUM (CASE WHEN (TABLETYPE='ET') AND (TRANSACTIONTYPE=20) THEN 1 ELSE 0 END) "HGS",
SUM (CASE WHEN (TABLETYPE='ET') AND (TRANSACTIONTYPE=20) THEN APPROVEDPRICE ELSE 0 END)/100 "HGS GELIR ",
SUM (CASE WHEN (TABLETYPE='EFV') THEN 1 ELSE 0 END) "IGB",
SUM (CASE WHEN (TABLETYPE='EFV') THEN APPROVEDPRICE ELSE 0 END)/100 "IGB GELIR ",
COUNT(*) "TOTAL",
SUM (APPROVEDPRICE/100) "TUM GELIR"
FROM kvk.sap_transactions_view
WHERE BANKCODE IN ('1','2','4','6','7')
AND PAYMENTDATE BETWEEN '20210801' AND '20211001'
GROUP BY BANKCODE, PAYMENTDATE
ORDER BY BANKCODE;

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