CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Pavithra Theva
Pavithra Theva

Reputation: 41

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

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

Upvotes: 4

Views: 19397

Answers (2)

Hans Henrik Eriksen
Hans Henrik Eriksen

Reputation: 2870

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);

Upvotes: 7

Aaron Zhang
Aaron Zhang

Reputation: 1

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

Upvotes: 0

Related Questions