Mysql – Subtract time difference between two columns in a table

debianMySQLmysql-5.5

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?

enter image description here

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)

Best Answer

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.

Related Question