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