CODEWITHSUNDEEP
mysql
Caitlin
Caitlin

Reputation: 95

Using Self-Join to find differences between rows

I have tried finding a solution to this question, but everything I've found has either asked a slightly different question or hasn't had an adequate answer. I have a table with the following setup:

    fullvna

    +--------------+-------------+------+-----+---------+----------------+
    | Field        | Type        | Null | Key | Default | Extra          |
    +--------------+-------------+------+-----+---------+----------------+
    | startdate    | date        | YES  |     | NULL    |                |
    | starttime    | time        | YES  |     | NULL    |                |
    | id           | int(11)     | NO   | PRI | NULL    | auto_increment |
    +--------------+-------------+------+-----+---------+----------------+

I want to find the time difference between each pair of consecutive lines, so the starttime of id=1 minus the starttime of id=2 (the table is ordered in reverse chronological order). I based my query off of what I found here: http://www.mysqltutorial.org/mysql-tips/mysql-compare-calculate-difference-successive-rows/

    create table difference as SELECT 
                one.id,
                one.starttime,
                two.starttime,
                (one.starttime - two.starttime) AS diff
            FROM
                fullvna one
                    INNER JOIN
                fullvna two ON two.id = one.id + 1;

I'm receiving the following printout, and am not sure what it means or what I'm doing wrong:

    ERROR 1064 (42000): 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 '  one.starttime,
        two.starttime,
        (one.starttime - two.starttime' at line 3

Upvotes: 3

Views: 2391

Answers (2)

Racil Hilan
Racil Hilan

Reputation: 25351

You have hidden characters that are displayed as spaces, but they're not and they're causing the error. Copy the query from my answer. And as Juan suggested, it is recommended to use the TIMEDIFF() function instead of subtracting them:

CREATE TABLE difference AS
SELECT one.id,
       one.starttime AS starttime,
       two.starttime AS endtime,
       TIMEDIFF(one.starttime, two.starttime) AS diff
FROM fullvna one
INNER JOIN fullvna two ON two.id = one.id + 1;

EDIT As xQbert mentioned, you need to use different names for the starttime column, so I corrected the query above accordingly.

Upvotes: 3

xQbert
xQbert

Reputation: 35323

  • Don't use alias one as it's a keyword pick a different one
  • alias startime as two columns with same name in a create table will not work.
  • timediff (as others mentioned in comments)

.

 CREATE TABLE difference as 
 SELECT a1.id
      , a1.starttime as OneStartTime
      , a2.starttime as TwoStartTime
      , TIMEDIFF(a1.starttime, a2.starttime) AS diff
 FROM fullvna a1
 INNER JOIN fullvna a2
    ON a2.id = a1.id + 1;

Upvotes: 3

Related Questions