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)

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

Paul White · Accepted Answer · 2015-09-26 08:29:59Z

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
– will, Commented Sep 26, 2015 at 2:42

Stack Exchange Network

Subtract time difference between two columns in a table

1 Answer 1

Hot Network Questions

Subtract time difference between two columns in a table

1 Answer 1

Related

Hot Network Questions