Find timestamp difference between 2 rows

General Discussion
1

Have a regular database with 3 columns. Index, Status (of machine), t_stamp (when the status changed. I cannot find the proper code to get the time difference from one row to the next.
I’ve tried DATETIME, LEAD, LAG and a multitude of different ways to try and evaluate the time difference from one row to the next. Can someone please show what I am missing?
This and versions fail:
SELECT bm06_status_ndx, status, t_stamp,
LAG(t_stamp, 1) OVER (ORDER BY bm06_status_ndx) AS time_diff
FROM bm06_status

database:

image
image1006×524 36.8 KB

2

What db are you using? Just so we’re on the same page. :slight_smile:

3

It’s hard to say without knowing what results you were getting with what you’ve tried, but I think you’re close.

SELECT bm06_status_ndx, 
       status, 
       t_stamp,
       t_stamp - LAG(t_stamp, 1) OVER (ORDER BY t_stamp) AS time_diff
FROM bm06_status
1 Like
4

Thanks Jordan, but still no joy. Using the default MSSQL database. I cannot get any results as I keep getting similar fault messages: The query could not be executed. Like you said, it really appears to be close but I cannot find the issue.

image
image1428×805 60.1 KB

5

Is it possible the LAG function does not exist? Sorry for such a beginner question, but i am just that, a beginner. I open MySQL Workbench and tried the code there and i get Error Code: 1305. FUNCTION LAG does not exist. Is there another way i can solve this query for the same results?

6

COuld we get and export of the table to make some test ?

7

It looks like MySQL is being used, not MSSQL. @pturmel will know the exact syntax to use, but this may help until he gets back to his computer:

8

Works on my install of MySQL. What version do you have? Window functions appeared in v8.0

9

The “LIMIT 1000” might be a clue.

10

De_Clerck_Arnaud: not having any luck exporting file to local laptop. Not sure if having a firewall issue or not, but trying to get a table loaded.
mcgheeiv: tried a couple different ways of writing a partition, according to that link, but still no luck.
JordanCClark: MySQL Workbench is 5.2.47

11

Ok, then you will need to either make a stored procedure, or you will need to script it.

12

Golly that sounds like fun! I sure do appreciate all the help and great feedback.
You all Rule!
Guess I’m off to learn how to do those items.
Thanks again.

1 Like
13

Upgrade your DB. MySQL 5.x is ancient and lacks numerous important features, like LAG and friends. While you are at it, consider switching to MariaDB–it is much more robust, by all accounts. (I’m a PostgreSQL fan, myself, but MariaDB is pretty nice.)

2 Likes
14

Ok i was trying on MSSQL. That's why it was working for me

15

pturmel: I just contacted our BT Group to see about upgrading the database.
If I get to choose, i will do as you suggested and ask for the MariaDB.
Sure appreciate all the advice from everyone. Thanks again.

16

@ilikebeer - A tip: use the @username syntax if you want to ping someone and have it appear in their inbox (as this should have for yours).

2 Likes