Select all data different second between two date times

1
votes

I have a mysql table with data connected to date and time. Each row has data and a date, like this:

datetime               name
2009-06-25 10:00:00    jhon
2009-06-25 10:00:05    jack
2009-06-25 10:00:11    json
2009-06-25 10:00:15    jack

My problem is I need to get the rows different second date1 and date2 like this :

datetime               name     second
2009-06-25 10:00:00    jhon     0
2009-06-25 10:00:05    jack     5
2009-06-25 10:00:11    json     6
2009-06-25 10:00:15    jack     4

Thank you

3
mysqldatetimebetween
Right, I'll remove the comment - Rehban Khatri
use self join to get this - Ankit Agrawal

3 Answers

1
votes

The functionality you are seeking is LAG, which is an available as an analytic function in many databases, though not in MySQL. However, you can simulate this functionality using session variables.

SET @dt = (SELECT MIN(UNIX_TIMESTAMP(datetime)) FROM yourTable);

SELECT t.datetime,
       t.name,
       t.curr_dt - t.lag_dt AS second
FROM
(
    SELECT @dt:=UNIX_TIMESTAMP(datetime) curr_dt,
           @dt lag_dt,
           datetime,
           name
    FROM yourTable
    ORDER BY datetime, name
) t
1
votes

You can find the nearest date and find the difference like below

select 
    datetime,
    name,
    TIME_TO_SEC(TIMEDIFF(datetime, prev_date)) diff
    FROM
    (
        SELECT 
        datetime,
        name,
        (select datetime from tbl t1 where t1.datetime<tbl.datetime order by  datetime desc limit 1) as prev_date
        FROM 
        `tbl`
      )tmp
0
votes

The result is :

[SQL]SET @dt = (SELECT MIN UNIX_TIMESTAMP(datetime) FROM gps_trs_location); [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(datetime) FROM gps_trs_location)' at line 1