Subtract time difference between two columns in a table

Question

Problem: How can I subtract the TIMEDIFF between two sensors (sensor 1 and sensor 2 in the example below) and return the the value in mili seconds?

How do I get with the resolution of milliseconds?

I am using the command line below to try to create the table with milliseconds resolution but returns error.

CREATE TABLE via1 
(
    id int auto_increment primary key, 
    sensor VARCHAR (10) NOT NULL, 
    date timestamp(6) default current_timestamp(6)
);

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 '(6) default current_timestamp(6))' at line 1

Server version: 5.5.44-0 + deb7u1 (Debian)

Answer 1

If you are only looking for the difference between two rows, you can reference them by their ids.

SELECT
  TIMESTAMPDIFF(MICROSECOND, s1.date, s2.date) DIV 1000
FROM 
  via1 s1
  , via1 s2 
WHERE 
  s1.id = 1
  AND
  s2.id = 2
;

There are a few errors in your CREATE TABLE statement:

CREATE TABLE via1 (
  id int auto_increment primary key, 
  sensor varchar(10) NOT NULL, 
  date timestamp(6) default current_timestamp(6)
);

You're using an old version of MySQL (5.5.44). From the docs:

11.3.6 Fractional Seconds in Time Values - MySQL 5.6 Reference Manual

Before MySQL 5.6.4, the instances are limited in which a fractional seconds part is permitted in temporal values. ...when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it.