如何解决函数datediff(unknown, timestamp without time zone, timestamp without time zone)不存在的问题

Question

SELECT "reviewedAt", "createdAt", DATEDIFF('hour', "createdAt"::timestamp, "reviewedAt"::timestamp) as hours_approved from "yadda$prod"."Application" 

错误[42883]：错误：函数datediff（unknown，timestamp without time zone，timestamp without time zone）不存在。提示：没有与给定名称和参数类型匹配的函数。您可能需要添加明确的类型转换。位置：36

- ChatGPT

---

对于捕捉“创建时”和“审核时”等时刻，你使用了错误的类型。对于时间线上的特定点，应使用带时区的时间戳。 - undefined

Answer 1

尝试这个：

SELECT 
"reviewedAt",
"createdAt",
DATE_PART('day', "reviewedAt"::timestamp - "createdAt"::timestamp) * 24 + DATE_PART('hour', "reviewedAt"::timestamp - "createdAt"::timestamp) AS hours_approved 
FROM "yadda$prod"."Application"

- nunoq

Answer 2

一种更多的解决方案:

SELECT 
    "reviewedAt",
    "createdAt",
    (EXTRACT(EPOCH FROM "reviewedAt"::timestamp - "createdAt"::timestamp)/3600)::int2 AS hours_approved
FROM "yadda$prod"."Application";

- Slava Rozhnev