SQL Lag() to Find Datetime Difference

I am trying to find the difference between Datetime entries in my sql table and was following this article – https://blog.jooq.org/2015/05/12/use-this-neat-window-function-trick-to-calculate-time-differences-in-a-time-series/.

In that example, everything works great and the delta shows as a timestamp. When I run it on my table, it seems to recognize the Datetime as an integer. For example-

2020-02-03 08:32:54
2020-02-03 08:35:19

The above gives a delta of 265 because the query is thinking it is 519-254=265 (last 3 digits of the timestamp). My Date field is of the type ‘Datetime’ and here is the query I was running:

SELECT Call_Created_By, Date,
       Date - lag(Date, 1) OVER (ORDER BY Date) Delta
FROM hcpc_visits_master
ORDER BY Date;

Thanks for the help.

Answer

Use timestampdiff():

SELECT Call_Created_By, Date,
       timestampdiff(second, lag(Date, 1) OVER (ORDER BY Date), date) as diff_seconds
FROM hcpc_visits_master
ORDER BY Date;

Advertisement

Answer