How can I use the LAG FUNCTION in Snowflake with TIMESTAMPS?

Question

Here is how my basic table is built:

I have users with various action dates as timestamps (Date, hour, minutes, seconds). The users' actions are either separated by a few days, or by a few hours. I am trying to find the intervals between each action for each user with the LAG function. My query in Snowflake works perfectly fine when I CAST my timestamps as DATES. When a user has 2 actions in the same day, my time interval is however=0. I want to see this time interval based on minutes (or seconds, doesn't matter). Here is the current query I am using in snowflake:

 SELECT 
    USERS,
  RANK() OVER(PARTITION BY USERS ORDER BY ACTION_DATE ASC) RowNumber,
  CAST(ACTION_DATE AS DATE),
  (CAST(ACTION_DATE AS DATE) - LAG(CAST(ACTION_DATE AS DATE)) OVER (PARTITION BY users ORDER BY ACTION_DATE)) AS TIME_INTERVAL
from TABLE1
ORDER BY 1,2,3;

As of now, this query works perfectly fine in Snowflake but I need to be able to get these time interval with my timestamps, and not just with my timestamps casted as dates.

The error I get in Snowflake is:

SQL compilation error: error line 6 at position 21 Invalid argument types for function '-': (TIMESTAMP_NTZ(9), TIMESTAMP_NTZ(9))

Does anybody know how I can use my LAG FUNCTION with timestamps or if there is a different function I should be using?

Answer 1

If you want the difference, then use datediff() or timestampdiff(). For seconds:

DATEDIFF(second,
         LAG(ACTION_DATE) OVER (PARTITION BY users ORDER BY ACTION_DATE),
         ACTION_DATE
        ) AS DIFF_SECONDS