Блог

У меня есть база данных со столбцом ВРЕМЕНИ, и я хотел бы сравнить округленную версию этого времени со столбцом ВРЕМЕНИ в другой таблице.

 Example:

root@localhost (mytest) desc ss;
 ------- ------ ------ ----- --------- ------- 
| Field | Type | Null | Key | Default | Extra |
 ------- ------ ------ ----- --------- ------- 
| t     | time | YES  |     | NULL    |       | 
 ------- ------ ------ ----- --------- ------- 
1 row in set (0.01 sec)

root@localhost (mytest) desc tt;
 ------- ------ ------ ----- --------- ------- 
| Field | Type | Null | Key | Default | Extra |
 ------- ------ ------ ----- --------- ------- 
| s     | time | YES  |     | NULL    |       | 
 ------- ------ ------ ----- --------- ------- 
1 row in set (0.00 sec)

root@localhost (mytest) select * from ss;
 ---------- 
| t        |
 ---------- 
| 16:00:00 | 
 ---------- 
1 row in set (0.00 sec)

root@localhost (mytest) select * from tt;
 ---------- 
| s        |
 ---------- 
| 15:45:00 | 
| 16:00:00 | 
| 19:30:00 | 
| 17:45:00 | 
| 18:00:00 | 
| 19:30:00 | 
 ---------- 
  

нужен запрос для объединения ss и tt, где округленный tt.s = ss.t

Попробуйте:

 SELECT * FROM tt JOIN ss ON HOUR(tt.s) = HOUR(ss.t);
  

Проверьте функции даты / времени MySQL

Моим решением было преобразовать в секунды, выполнить математику, а затем преобразовать обратно во ВРЕМЯ.

 select s,hour(s)*3600 minute(s)*60 second(s) time_as_seconds from tt;
 ---------- ----------------- 
| s        | time_as_seconds |
 ---------- ----------------- 
| 15:45:00 |           56700 | 
| 16:00:00 |           57600 | 
| 19:30:00 |           70200 | 
| 17:45:00 |           63900 | 
| 18:00:00 |           64800 | 
| 19:30:00 |           70200 | 
 ---------- ----------------- 
6 rows in set (0.00 sec)
  

Для небольшого набора данных я бы просто вычислил значения «секунд» из каждой таблицы и сравнил. Итак, я бы в конечном итоге присоединился:

 1800 * round((hour(tt.s)*3600 minute(tt.s)*60 second(tt.s)) / 1800)
  

против

 hour(ss.t)*3600 minute(ss.t)*60 second(ss.t)
  

Но если в справочной таблице (t) много строк или в t.ss есть индекс, который может ускорить запрос, нам пришлось бы возвращать значения времени обратно. SEC_TO_TIME решает этот аспект.

Итак, вот пример округления до ближайшего получаса (отсюда 1800)

 select 
 s, 
 hour(s)*3600 minute(s)*60 second(s) time_as_seconds,
 1800 * round((hour(s)*3600 minute(s)*60 second(s)) / 1800) rounded_to_half_hour,
 sec_to_time(1800 * round((hour(s)*3600 minute(s)*60 second(s)) / 1800)) rounded_time
from tt;

 ---------- ----------------- ---------------------- -------------- 
| s        | time_as_seconds | rounded_to_half_hour | rounded_time |
 ---------- ----------------- ---------------------- -------------- 
| 15:45:00 |           56700 |                57600 | 16:00:00     | 
| 16:00:00 |           57600 |                57600 | 16:00:00     | 
| 19:30:00 |           70200 |                70200 | 19:30:00     | 
| 17:45:00 |           63900 |                64800 | 18:00:00     | 
| 18:00:00 |           64800 |                64800 | 18:00:00     | 
| 19:30:00 |           70200 |                70200 | 19:30:00     | 
 ---------- ----------------- ---------------------- -------------- 
6 rows in set (0.00 sec)
  

Предполагая, что ss.t это уже округлено:

 SELECT *
FROM tt
  JOIN ss
  ON    ( SUBTIME(ss.t, '00:30:00.0') <= tt.s )
    AND ( tt.s < ADDTIME(ss.t, '00:30:00.0')  )