Milan van Dijck Milan van Dijck - 6 months ago 8
SQL Question

MySQL Getting the return of a substraction

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.

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