MySQL вибрати DATETIME аналогічно до хвилини

Я повинен одержувати результати порівняно з тим самим часом між двома таблицями, але часові штампи відрізняються від секунди через те, як вони були записані. Я хотів би отримати результат, як у Прикладі 1 , але я отримую лише значення зі зірочкою, як у Прикладі 2 . Який найкращий спосіб видалити secods з порівняння, або вибрати значення, яке відповідає найближчому значенню DATETIME?

В даний час я використовую цей запит:

SELECT Table1.TimeSTamp1, Table1.Param1, Table2.TimeStamp2, Table2.Param2 
    FROM Table1, Table2 
    WHERE ... conditions for the other parameters of Table1 and Table2... 
    AND Table1.TimeSTamp1 = Table2.TimeStamp2

Будь-яке пропозицію про найкращу практику тепло вітається.


Приклад 1

TimeStamp1          ¦   Param1  ¦   TimeStamp2          ¦   Param2
2011-01-01 00:00:35 ¦   1       ¦   2011-01-01 00:00:35 ¦   a       *
2011-01-01 00:01:35 ¦   2       ¦   2011-01-01 00:01:35 ¦   b
2011-01-01 00:02:37 ¦   3       ¦   2011-01-01 00:02:35 ¦   c
2011-01-01 00:03:31 ¦   4       ¦   2011-01-01 00:03:35 ¦   d
2011-01-01 00:04:32 ¦   5       ¦   2011-01-01 00:04:35 ¦   e
2011-01-01 00:05:38 ¦   6       ¦   2011-01-01 00:05:35 ¦   f
2011-01-01 00:06:36 ¦   7       ¦   2011-01-01 00:06:36 ¦   g       *
2011-01-01 00:07:32 ¦   8       ¦   2011-01-01 00:07:35 ¦   h
2011-01-01 00:08:33 ¦   9       ¦   2011-01-01 00:08:35 ¦   i
2011-01-01 00:09:33 ¦   10      ¦   2011-01-01 00:09:33 ¦   l       *
2011-01-01 00:10:35 ¦   11      ¦   2011-01-01 00:10:35 ¦   m       *
2011-01-01 00:11:29 ¦   12      ¦   2011-01-01 00:11:31 ¦   n

lll Example 2

TimeStamp1          ¦   Param1  ¦   TimeStamp2          ¦   Param2
2011-01-01 00:00:35 ¦   1       ¦   2011-01-01 00:00:35 ¦   a
2011-01-01 00:06:36 ¦   7       ¦   2011-01-01 00:06:36 ¦   g
2011-01-01 00:09:33 ¦   10      ¦   2011-01-01 00:09:33 ¦   l
2011-01-01 00:10:35 ¦   11      ¦   2011-01-01 00:10:35 ¦   m
7

3 Відповіді

Це вираз MySql дасть вам повернення значення DATETIME з секундами з нульовим значенням.

CONVERT(DATE_FORMAT(table.column,'%Y-%m-%d-%H:%i:00'),DATETIME)

Погляньте на це. http://dev. mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format . Таким чином, ви можете виконати такий запит:

SELECT Table1.TimeSTamp1, Table1.Param1, Table2.TimeStamp2, Table2.Param2 
    FROM Table1
    JOIN Table2 ON  CONVERT(DATE_FORMAT(Table1.TimeStamp1,'%Y-%m-%d-%H:%i:00'),DATETIME)
                 =  CONVERT(DATE_FORMAT(Table2.TimeStamp2,'%Y-%m-%d-%H:%i:00'),DATETIME)
    WHERE ... conditions for the other parameters of Table1 and Table2... 

Але будьте обережні. Автогенеровані мітки часу є на зразок числа з плаваючою точкою; коли два з них стають рівними одна одній, це просто удача. Зменшення міток часу до хвилини може бути добре, але ви також можете краще віднімати одну відмітку часу від іншого та порівнювати відмінності (або абсолютні значення відмінностей).

Крім того, це приєднання буде повільним, оскільки він повинен виконувати функцію другого обтинання для кожного значення, тому не може використовувати будь-які індекси.

Ви можете відняти одну мітку від іншого з TIMESTAMPDIFF() . Але будьте обережні. Ця функція працює лише на рівні секунд для міток часу протягом декількох днів друг від друга; вона безповоротно переливається (як я відкрив з великим болем).

Ви можете спробувати обрізати мітки часу до хвилин, коли ви вставляєте їх. Це дозволить вам індексувати їх.

10
додано
Дякую, TIMESTAMPDIFF робить роботу. іноді я отримую подвійні записи і видалення їх займає багато часу. Я перевіряю, чи швидше перетворення дат у цьому випадку.
додано Автор Andrew Strathclyde, джерело

Ви можете використовувати TIMESTAMPDIFF щоб обчислити різницю між датами у секундах:

SELECT t1.TimeStamp1, t1.Param1, t2.TimeStamp2, t2.Param2
FROM Table1 t1
LEFT JOIN Table2 t2
ON ABS(TIMESTAMPDIFF(SECOND,t1.TimeStamp1,t2.TimeStamp2))<=4
WHERE ...

Відзначаючи попередження Оллі Джонса , я вже протестований TIMESTAMPDIFF в MySQL версії 5.1.58 і не виявив жодного переповнення з тимчасовими мітками, що відрізняються щонайменше на 10 000 років. Можливо, ця проблема була виправлена.

1
додано
WHERE ...
AND ABS(UNIX_TIMESTAMP(TimeStamp1) - UNIX_TIMESTAMP(TimeStamp2)) < :threshold:

де порог - це кількість секунд, після чого ви більше не хочете відповідати (наприклад, 60 протягом 1 хвилини).

1
додано
Я пробував, але це займає надто багато часу, поки не з'явиться повідомлення про помилку 2013 року
додано Автор Andrew Strathclyde, джерело