Interval to previous row

I would like to get the interval between the rows in my table.

When I use this query I get seconds from previous timestamp. But the result is only right when two timestamps is in the same minute.

SELECT 
    Time_Stamp, 
    State,
    Time_Stamp - LAG(Time_Stamp) OVER(ORDER BY Time_Stamp)
AS Seconds_In_State
FROM State_Table
ORDER BY Time_Stamp; 

How do I get the result in intervals?

Answer

you can use timestampdiff() function. I have used minute as unit here.

The unit should be one of the following : FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. Unit is the measurement unit here in which you want to count the difference.

SELECT 
    Time_Stamp, 
    State,
    TIMESTAMPDIFF(minute, LAG(Time_Stamp) OVER(ORDER BY Time_Stamp),Time_Stamp)
AS Minute_In_State
FROM State_Table
ORDER BY Time_Stamp;