Im trying to add a date difference calculation to a Redshift SQL query:
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
... and I'm getting an error:
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.
Any suggestions?
2 Answers
The DATEDIFF Function is documented as:
DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} )
Thus, it can either take a date or a timestamp.
Your query is passing a timestamp with time zone, hence the error.
Therefore, you could use:
DATEDIFF(MINUTE, session_start::timestamp, session_finish::timestamp)
Solution without datediff:
select
(
cast(extract(epoch from to_timestamp(min(event_start), 'YYYY-MM-DD HH24:MI:SS')) as integer) -
cast(extract(epoch from to_timestamp(max(event_finish), 'YYYY-MM-DD HH24:MI:SS') ) as integer)
) / 60 as session_duration_minutes
FROM usage_log
The solution converts the timestamps to unix epoch, subtracts and truncates to floor(minutes).
