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.

    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)

  • Each row in your question is a sensor? line 1 = sensor 1 and line 2 = sensor 2?
    – Erik
    Sep 25, 2015 at 22:47
  • Yes, each line refers to a sensor. (translated from Portuguese by a moderator)
    – will
    Sep 25, 2015 at 22:57

1 Answer 1


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

  TIMESTAMPDIFF(MICROSECOND, s1.date, s2.date) DIV 1000
  via1 s1
  , via1 s2 
  s1.id = 1
  s2.id = 2

There are a few errors in your CREATE TABLE statement:

  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.

  • After upgrade the database to version 5.6.26 everything worked well; )
    – will
    Sep 26, 2015 at 2:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.