CODEWITHSUNDEEP
sqlite
ChatterOne
ChatterOne

Reputation: 3541

Date difference between two separate rows in SQLite with no ID

I have data in SQLite like this (a few thousands of rows):

1536074432|startRecording
1536074434|stopRecording
1536074443|startRecording
1536074447|stopRecording
1536074458|startRecording
1536074462|stopRecording

And I'd like to get the amounts of seconds passed between two consecutive distinct events (basically how many seconds of video I've recorded).

I know about another similar question ( Date Difference between consecutive rows ), but in my case it's different because I cannot get the "next" row by ID, but I have to get it based on a different event name.

There is an answer that works magic, but it's specific to SQL Server ( Query to find the time difference between successive events ), and I need this for SQLite.

I could do this in Oracle with the LAG / LEAD functions, but no idea how to do it in SQLite.

I could also do this with a separate parsing script, but I think it would be more efficient to be able to do this directly from a query.

Upvotes: 1

Views: 1053

Answers (1)

DinoCoderSaurus
DinoCoderSaurus

Reputation: 6520

Even though there is no id in the table, sqlite stores a rowid (from sqlite CREATE_TABLE doc):

ROWIDs and the INTEGER PRIMARY KEY
Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "rowid" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "rowid", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.

Assuming perfectly clean data as described :) how about:

select a.rowid,a.time,a.event,b.rowid,b.time,b.event,b.time - a.time as elapsed --,sum(b.time-a.time)
from t2 a, t2 b
where a.rowid % 2 = 1
and b.rowid = a.rowid + 1

Upvotes: 4

Related Questions