amazon-web-services - 如何使用 Redshift DATEDIFF？

Question

我正在尝试向 Redshift SQL 查询添加日期差异计算:

SELECT 
   client,
   session,
   person_id,
   min(event_start) as "session_start",
   max(event_finish) as "session_finish",
   sum (case when event_type = 'execute-query-action-1'  then 1 else 0 end) as "Type1 reports run" ,
   sum (case when event_type = 'execute-query-action-2'  then 1 else 0 end) as "Type 2 reports run" ,
   DATEDIFF(MINUTE,session_start,session_finish) as "session_duration_minutes"
FROM usage_log
group by client,person_Id,session

...我收到一个错误:

function pg_catalog.date_diff("unknown", timestamp with time zone, timestamp with time zone) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts.

有什么建议？

Answer 1

DATEDIFF Function记录为:

DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} )

因此，它可以采用 日期 或 时间戳 .

您的查询正在传递 带时区的时间戳 ，因此错误。

因此，您可以使用:

DATEDIFF(MINUTE, session_start::timestamp, session_finish::timestamp)