SQL query to retrieve records closest to timestamp

Question

I'm trying to retrive the records from a table in my MySQL database, where:

• the timestamp is the closest to a variable I provide; and,
• grouped by the fields keyA, keyB, keyC and keyD

I've hard coded the variable as below to test this, however can not get the query to work.

SQLFiddle

My current schema is:

CREATE TABLE dataHistory (
          timestamp datetime NOT NULL,
          keyA varchar(10) NOT NULL,
          keyB varchar(10) NOT NULL,
          keyC varchar(25) NOT NULL,
          keyD varchar(10) NOT NULL,
          value int NOT NULL,
          PRIMARY KEY (timestamp,keyA,keyB,keyC,keyD)
          );

INSERT INTO dataHistory
    (timestamp, keyA, keyB, keyC, keyD, value)
VALUES
    ('2016-05-12 04:15:00', 'value1', 'all', 'value2', 'domestic', 96921),
    ('2016-05-12 04:05:00', 'value1', 'all', 'value2', 'domestic', 96947),
    ('2016-05-12 04:20:00', 'value1', 'all', 'value2', 'domestic', 96954),
    ('2016-05-12 04:15:00', 'value1', 'all', 'value3', 'domestic', 2732),
    ('2016-05-12 04:10:00', 'value1', 'all', 'value3', 'domestic', 2819),
    ('2016-05-12 04:20:00', 'value1', 'all', 'value3', 'domestic', 2802);

and the query I currently have is:

SELECT e.difference, e.timestamp, e.keyA, e.keyB, e.keyC, e.keyD, e.value
FROM (SELECT TIMESTAMPDIFF(minute, '2016-05-12 04:11:00', d.timestamp) as difference, d.timestamp, d.keyA, d.keyB, d.keyC, d.keyD, d.value
    FROM dataHistory d
    GROUP BY d.keyA, d.keyB, d.keyC, d.keyD) as e;

All I can seem to extract from the sample data is the earliest two records and not the two closest to the datetime. What I receive:

difference  timestamp   keyA    keyB    keyC    keyD    value
-10 May, 12 2016 04:05:00   value1  all value2  domestic    96947
-5  May, 12 2016 04:10:00   value1  all value3  domestic    2819

I am expecting to see:

timestamp   keyA    keyB    keyC    keyD    value
May, 12 2016 04:15:00   value1  all value2  domestic    96921
May, 12 2016 04:10:00   value1  all value3  domestic    2819

Any assistance would be appreciated!

Answer 1

SELECT e.difference, e.timestamp, e.keyA, e.keyB, e.keyC, e.keyD, e.value
FROM (SELECT ABS(TIMESTAMPDIFF(minute, '2016-05-12 04:11:00', d.timestamp)) as difference, d.timestamp, d.keyA, d.keyB, d.keyC, d.keyD, d.value
    FROM dataHistory d
    ORDER BY difference) as e
GROUP BY e.keyA, e.keyB, e.keyC, e.keyD;

This query is returning the values you want.