Geek Answers Handbook

SQL query to get records closest to timestamp

I am trying to retrieve records from a table in my MySQL database, where:

  • the timestamp is closest to the variable that I am providing; and,
  • grouped by keyA, keyB, keyC and keyD fields

I checked this variable as shown below, but cannot make the request 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 that I have is the following:

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 extract from the sample data is the earliest two records, not the two closest to the date. What I get:

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 expect 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 help would be appreciated!

+4
source share
3 answers
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 returns the desired values.

+3
source

Does it help?

SELECT
  TIMESTAMPDIFF (MINUTE , '2016-05-12 04:15:00' , MainTable.timestamp) AS Difference ,
  MainTable.timestamp ,
  MainTable.KeyA ,
  MainTable.KeyB ,
  MainTable.KeyC ,
  MainTable.KeyD ,
  MainTable.value
FROM
  dataHistory AS MainTable
LEFT OUTER JOIN
  dataHistory AS SecondaryTable
ON
  MainTable.KeyA = SecondaryTable.KeyA
AND
  MainTable.KeyB = SecondaryTable.KeyB
AND
  MainTable.KeyC = SecondaryTable.KeyC
AND
  MainTable.KeyD = SecondaryTable.KeyD
AND
  ABS (TIMESTAMPDIFF (MINUTE , '2016-05-12 04:15:00' , MainTable.timestamp)) > ABS (TIMESTAMPDIFF (MINUTE , '2016-05-12 04:15:00' , SecondaryTable.timestamp))
WHERE
  SecondaryTable.timestamp IS NULL;

, Data Professional, - , http://www.madeiradata.com

0

, , - . timestamp . - , . ? ? . . , , . MIN, .

:

: .

select 
  keya,
  keyb,
  keyc,
  keyd,
  min(abs(timestampdiff(minute, '2016-05-12 04:11:00', d.timestamp))) as difference
from datahistory 
group by keya, keyb, keyc, keyd;

:. .

select 
  best.difference, 
  dh.timestamp, 
  best.keyA, 
  best.keyB, 
  best.keyC, 
  best.keyD, 
  dh.value
from
(
  select 
    keya, keyb, keyc, keyd,
    min(abs(timestampdiff(minute, '2016-05-12 04:11:00', timestamp))) as difference
  from datahistory 
  group by keya, keyb, keyc, keyd
) best
join datahistory dh 
  on  dh.keya = best.keya and dh.keyb = best.keyb 
  and dh.keyc = best.keyc and dh.keyd = best.keyd
  and abs(timestampdiff(minute, '2016-05-12 04:11:00', dh.timestamp)) = best.difference
order by best.keyA, best.keyB, best.keyC, best.keyD;

SQL: http://sqlfiddle.com/#!9/a6004b/10

(Replace '2016-05-12 04:11:00'with now()in your real request.)

0
source

More articles:


All Articles