Find the closest timestamp SQL?

Question

I have two tables that look like this:

Communication: (Calls made)

Timestamp            FromIDNumber ToIDNumber GeneralLocation 
2012-03-02 09:02:30  878          674        Grasslands 
2012-03-02 11:30:01  456          213        Tundra 
2012-03-02 07:02:12  789          654        Mountains

Movement:

Timestamp            IDNumber Type        X   Y  
2012-03-02 11:02:30  379      pedestrian  32  46
2012-03-01 12:32:41  654      pedestrian  54  56
2012-03-02 07:02:16  789      pedestrian  39  52

And the following query:

SELECT c.FromIDNumber, m.Timestamp, m.X, m.Y
FROM Communication c
JOIN Movement m ON c.FromIDNumber = m.IDNumber
WHERE m.TimeStamp = (SELECT MIN(mm.Timestamp)
                 FROM Movement mm
                 WHERE mm.TimeStamp >= c.TimeStamp)

Basically, it goes through every row of Commmunication and finds the location of a call made by finding the closest movement timestamp for a given communication timestamp. (Here's the SQL Fiddle).

The problem for me is that it only returns the closest movement timestamp that is after a certain communication timestamp. For example, given this simple example:

 Communication Time: 
 1:10 pm 

 Movement Data: 
 1:09 pm   (100,100)
 1:15 pm   (200,200)

The query will return the 1:15pm (200,200), even though the closest timestamp is the 1:09 pm. How would I write an sql query to find the closest time? I found a kinda similar SO post for sql-server, but how would I do this in MySQL?

I just don't have much experience in SQL, so help would be greatly appreciated thanks!!!

I knew it [sounded familiar](http://stackoverflow.com/questions/30408106/how-to-run-a-specific-sql-query-for-every-row-in-the-table). — PM 77-1, Jun 04 '15 at 22:20
Take the difference of all the timestamps with your timestamp of interest and look for the minimum absolute value. — ThatOneDude, Jun 04 '15 at 22:23
What @ssnobody said and specifically, `ABS(UNIX_TIMESTAMP(timestamp1)-UNIX_TIMESTAMP(timestamp2))`. — Turophile, Jun 04 '15 at 22:28
Oh I see.. thanks!! I think that'll get me started, and I can probably figure it out from there — ocean800, Jun 04 '15 at 22:31
@Turophile - It seems that [TIMESTAMPDIFF()](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff) will give a more compact solution. — PM 77-1, Jun 04 '15 at 22:33

score 4 · Accepted Answer · answered Jun 05 '15 at 00:00

4

I think you want this version:

SELECT c.FromIDNumber, m.Timestamp, m.X, m.Y
FROM Communication c
JOIN Movement m ON c.FromIDNumber = m.IDNumber
WHERE m.TimeStamp = (SELECT mm.Timestamp
                     FROM Movement mm
                     ORDER BY abs(mm.TimeStamp - c.TimeStamp)
                     LIMIT 1
                    );

You may need to use timestampdiff() instead of - depending on the types of the column.

answered Jun 05 '15 at 00:00

Gordon Linoff

1,242,037
58
646
786

Thanks that works really well!! Just wondering, I keep on getting " `BIGINT UNSIGNED value is out of range in '(`DBName.mm.timestamp - DBName.c.timestamp)' ` " Do you know the reason for this? – ocean800 Jun 08 '15 at 18:46
1

@ocean800 . . . That happens when you have negative values. You can rewrite it as `order by (greatest(mm.TimeStamp, c.TimeStamp) - least(mm.TimeStamp, c.TimeStamp))`. – Gordon Linoff Jun 08 '15 at 22:31
Oh, I see.. in the end, I just used timestampdiff(), but thanks!! – ocean800 Jun 08 '15 at 23:11

Find the closest timestamp SQL?

1 Answers1