Snowflake SQL error - Invalid argument types for function '-': (TIMESTAMP_NTZ(9), TIMESTAMP_NTZ(9))

user_12178726 0
Viewed 7564

I get this error when I try to subtract the timestamps and do a window function (lead, lag and partition by):

Invalid argument types for function '-': (TIMESTAMP_NTZ(9), TIMESTAMP_NTZ(9))

Tried date_diff, but that doesn't work along with window function

SELECT 
   user_id,
   event,
   received_at,
   received_at - LAG( received_at,1) OVER (PARTITION BY user_id ORDER BY received_at) AS last_event
FROM 
   segment_javascript.help_center_opened
snowflake-cloud-data-platform sql
2 Answers

You can't do it the "Oracle way" by just subtracting two dates to get a number, you must use a diff function with a unit/scale of measure, eg: 

SELECT 
   ts, 
   TIMESTAMPDIFF(MILLISECONDS, LAG(ts, 1) OVER (ORDER BY ts), ts) tsd
FROM 
   (VALUES (CURRENT_TIMESTAMP), (DATEADD(DAY, 1, CURRENT_TIMESTAMP))) v(ts);
user_121664970

I suppose that you need to calcuate the time difference for the same user_id with last event received time.

If so, I think this would work:

SELECT 
   user_id,
   event,
   received_at,
   DATEDIFF(
     MINUTE, -- or any other supported date/time part
     received_at, -- start time 
     LAG( received_at,1) OVER (PARTITION BY user_id ORDER BY received_at) -- end time
     ) AS last_event
FROM 
   segment_javascript.help_center_opened
user_89890100
Related