SQL datetime calculations

Suppose I have a table as follows:

CREATE TABLE data ( `datetime` datetime DEFAULT NOT NULL, `temperature` float DEFAULT NO NULL );

populated with temperature samples of a couple times a second.

Let's say I want to find the temperatures which are 1 second apart:

SELECT D1.datetime, D2.datetime, D1.temperature, D2.temperature FROM data AS D1, data AS D2 WHERE D1.datetime - D2.datetime = 1;

And if I want to find the temperatures which are 1 minute apart:

SELECT D1.datetime, D2.datetime, D1.temperature, D2.temperature FROM data AS D1, data AS D2 WHERE D1.datetime - D2.datetime = 100;

What I dont understand is, why there is a multiplier of 100 and not 60, since there are only 60 seconds in a minute?

What's the definition of 'datetime'? It would be difficult to say without knowing anything about the data type.

You haven't mentioned it, but I believe your database is MySQL. (At least it's not Oracle, which behaves way differently than this.)

When you add a number to a DATETIME value in MySQL, it gets converted to a numeric double value, with a microseconds part of .000000, i.e. YYYYMMDDHHMISS.000000.
In this numeric value, an increase of 100 corresponds to an interval of 1 minute of the datetime. You can test that by casting such a numeric value back to DATETIME. (If it is invalid, the CAST will return NULL.)

mysql> 
mysql> 
mysql> select d, d+0 d0, d+60 d2, d+100 d4,
    ->        cast(d+60  as datetime) c1,
    ->        cast(d+100 as datetime) c2
    ->  from  (select cast('2009-01-01 10:11:12' as datetime) d) t;
+---------------------+-----------------------+-----------------------+-----------------------+------+---------------------+
| d                   | d0                    | d2                    | d4                    | c1   | c2                  |
+---------------------+-----------------------+-----------------------+-----------------------+------+---------------------+
| 2009-01-01 10:11:12 | 20090101101112.000000 | 20090101101172.000000 | 20090101101212.000000 | NULL | 2009-01-01 10:12:12 | 
+---------------------+-----------------------+-----------------------+-----------------------+------+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 
mysql> 

A correct way to work with DATETIME differences is to use the INTERVAL keyword.

Your query should've been this -

mysql> 
mysql> 
mysql> desc t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| dtime | datetime | NO   |     | NULL    |       | 
| temp  | float    | NO   |     | NULL    |       | 
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select dtime, temp from t;
+---------------------+------+
| dtime               | temp |
+---------------------+------+
| 2009-01-01 06:07:08 | 70.4 | 
| 2009-01-01 07:08:09 | 72.6 | 
| 2009-01-01 08:09:10 | 69.3 | 
| 2009-01-01 06:08:08 | 62.1 | 
| 2009-01-01 07:08:10 | 63.2 | 
| 2009-01-01 11:12:13 | 64.3 | 
| 2009-01-01 06:09:08 | 78.8 | 
| 2009-01-01 07:08:11 | 79.9 | 
+---------------------+------+
8 rows in set (0.00 sec)

mysql> 
mysql> # fetch all pairs of records that have a difference of 1 minute between their DTIME values
mysql> 
mysql> SELECT D1.dtime, D2.dtime, D1.temp, D2.temp FROM t AS D1, t AS D2 WHERE D1.dtime = D2.dtime + interval 1 minute;
+---------------------+---------------------+------+------+
| dtime               | dtime               | temp | temp |
+---------------------+---------------------+------+------+
| 2009-01-01 06:08:08 | 2009-01-01 06:07:08 | 62.1 | 70.4 | 
| 2009-01-01 06:09:08 | 2009-01-01 06:08:08 | 78.8 | 62.1 | 
+---------------------+---------------------+------+------+
2 rows in set (0.00 sec)

mysql> 
mysql> 

HTH,
tyler_durden

Database is MySQL indeed, overlooked that it would matter. Thanks for your elaborate answer.