4

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)

  • Each row in your question is a sensor? line 1 = sensor 1 and line 2 = sensor 2? – Erik Sep 25 '15 at 22:47
  • Yes, each line refers to a sensor. (translated from Portuguese by a moderator) – will Sep 25 '15 at 22:57
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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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