MySQL Getting the return of a substraction

Question

We are trying to get the length of a physical exercise by using the timestamps on our sensor data.

We currently have the following query:

SELECT UNIX_TIMESTAMP(
    SELECT HAAS2.trainingsdata.timestamp
    FROM HAAS2.trainingdata
    WHERE HAAS2.trainingsdata.training_id= 1
    ORDER BY timestamp DESC LIMIT 1)
- UNIX_TIMESTAMP(
    SELECT HAAS2.trainingsdata.timestamp
    FROM HAAS2.trainingdata
    WHERE HAAS2.trainingsdata.training_id= 1
    ORDER BY timestamp ASC LIMIT 1)
AS output

(enters added for readability)

When testing this query in phpMyAdmin we get the following error:

#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 'SELECT HAAS2.trainingsdata.timestamp FROM HAAS2.trainingdata WHERE HAAS2.trainin' at line 1

We've tried different ways to write down the query all resulting in the same error. We don't understand where the syntax error lies.

Although you are indeed better off with a different query *(the answer below)*, there is a direct answer to your explicit question; The sub-queries need to be within their own brackets. `SELECT UNIX_TIMESTAMP( ( ) ) - UNIX_TIMESTAMP( ( ) ) AS output` The brackets you had were only for the `UNIX_TIMESTAMP()` function call, you *also* needed the ones around the sub-query. — MatBailie, Jun 01 '16 at 10:37

score 3 · Accepted Answer · answered Jun 01 '16 at 10:30

3

SELECT max(UNIX_TIMESTAMP(timestamp)) -
       min(UNIX_TIMESTAMP(timestamp)) AS output
FROM HAAS2.trainingdata
WHERE training_id = 1

answered Jun 01 '16 at 10:30

juergen d

201,996
37
293
362

MySQL Getting the return of a substraction

1 Answers1