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

user_15268236 0
Viewed 3364

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?

snowflake-cloud-data-platform lag sql
2 Answers

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
user_11440350

You need to use timestampdiff or datediff since you cannot minus two timestamps by using the - operator. Here is a reproducible example that shows how to do it for seconds, minutes and hours.

create or replace transient table users
(
    users       varchar,
    action_date timestamp_ntz
);

insert overwrite into users
values ('simon', '2020-01-01T01:00:00'),
       ('simon', '2020-01-01T02:00:00'),
       ('simon', '2020-01-02T01:00:00'),
       ('simon', '2020-01-02T02:00:00'),
       ('simon', '2020-01-03T01:00:00'),
       ('simon', '2020-01-04T01:00:00'),
       ('jen', '2020-01-01T01:00:00'),
       ('jen', '2020-01-02T01:00:00'),
       ('jen', '2020-01-03T01:00:00'),
       ('jen', '2020-01-04T01:00:00')
;

SELECT
    USERS                                                                                 as users,
    action_date                                                                           as action_date,
    RANK() OVER (PARTITION BY USERS ORDER BY ACTION_DATE ASC)                             as row_number,
    timestampdiff('minutes', action_date, LAG(action_date) OVER (PARTITION BY users ORDER BY action_date)) AS minutes_interval,
    timestampdiff('seconds', action_date, LAG(action_date) OVER (PARTITION BY users ORDER BY action_date)) AS seconds_interval,
    timestampdiff('hours', action_date, LAG(action_date) OVER (PARTITION BY users ORDER BY action_date)) AS hours_interval
from USERS
ORDER BY 1, 2, 3;

The above produces:

+-----+-----------------------------+----------+----------------+----------------+--------------+
|USERS|ACTION_DATE                  |ROW_NUMBER|MINUTES_INTERVAL|SECONDS_INTERVAL|HOURS_INTERVAL|
+-----+-----------------------------+----------+----------------+----------------+--------------+
|jen  |2020-01-01 01:00:00.000000000|1         |NULL            |NULL            |NULL          |
|jen  |2020-01-02 01:00:00.000000000|2         |-1440           |-86400          |-24           |
|jen  |2020-01-03 01:00:00.000000000|3         |-1440           |-86400          |-24           |
|jen  |2020-01-04 01:00:00.000000000|4         |-1440           |-86400          |-24           |
|simon|2020-01-01 01:00:00.000000000|1         |NULL            |NULL            |NULL          |
|simon|2020-01-01 02:00:00.000000000|2         |-60             |-3600           |-1            |
|simon|2020-01-02 01:00:00.000000000|3         |-1380           |-82800          |-23           |
|simon|2020-01-02 02:00:00.000000000|4         |-60             |-3600           |-1            |
|simon|2020-01-03 01:00:00.000000000|5         |-1380           |-82800          |-23           |
|simon|2020-01-04 01:00:00.000000000|6         |-1440           |-86400          |-24           |
+-----+-----------------------------+----------+----------------+----------------+--------------+
user_32872500
Related