SQL - How to find difference between two rows of same column containing timestamps

53
February 16, 2019, at 11:20 PM

I have a table name as test containing below two colums

name    GMTtime
Test1   2019-02-01 19:54:45.507
Test2   2019-02-15 19:54:55.537
Test3   2019-02-15 19:55:05.560
Test4   2019-02-15 19:55:15.580

Question: I want to list rows of GMTtime if difference between these timestamps are more than 15secs (e.g. diff between "2019-02-01 19:54:45.507" and 2019-02-15 19:54:55.537 is not more than 15seconds)

Answer 1

In standard SQL, you would use lag() and date comparisons:

select t.*
from (select t.*, lag(t.GMTtime) over (order by t.GMTtime) as prev_GMTtime
      from t
     ) t
where prev_GMTtime < GMTtime - interval '15 second'
Answer 2

use the function : from_unixtime()

Answer 3

If you're running MySQL 5.7 or below (i.e. a version of SQL that doesn't support LAG), you can get the results you need by JOINing your table to itself using the condition that the time for the second table is the latest time less than the time from the first table. Then you can compare the two times using TIMESTAMPDIFF to find those more than 15 seconds apart:

SELECT *
FROM test t1
JOIN test t2 ON t2.GMTtime = (SELECT MAX(GMTtime) 
                              FROM test 
                              WHERE test.GMTtime < t1.GMTtime)
WHERE TIMESTAMPDIFF(SECOND, t2.GMTtime, t1.GMTtime) > 15

Output:

name   GMTtime               name   GMTtime
Test2  2019-02-15 19:54:56   Test1  2019-02-01 19:54:46

Demo on dbfiddle

READ ALSO
Do very small MySQL tables ignore indexes?

Do very small MySQL tables ignore indexes?

After turning on log_queries_not_using_indexes, I noticed that one query was rapidly filling up the slow query log:

67
How can I insert an array to database table with new rows

How can I insert an array to database table with new rows

I'm trying to insert an array size 2000 into a database table that contains 13 columns, from array[0] to array[12] in the first row then from array[13] to array[25] in the second row

82
Append a new field to an existing array

Append a new field to an existing array

I'm trying to add the same team name to each entry in the arrayA user can currently add multiple names in a single form submission

63
Write a SQL query to list the recording id and recording date of all recordings that appear on all of the cds in the cd table

Write a SQL query to list the recording id and recording date of all recordings that appear on all of the cds in the cd table

I've probably tried writing this query 30 different ways, I and just can't seem to figure it outHere is the problem:

72