How do I round a MySQL TIME to the nearest hour (not a DATE TIME / TIMESTAMP)

I have a database with a TIME column, and I'd like to compare the rounded version of that time to a TIME column in another table.

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;
+----------+
| 开发者_StackOverflow中文版s        |
+----------+
| 15:45:00 | 
| 16:00:00 | 
| 19:30:00 | 
| 17:45:00 | 
| 18:00:00 | 
| 19:30:00 | 
+----------+

need a query to join ss and tt where rounded tt.s = ss.t

Try:

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

Checkout the MySQL Date/Time Functions

My solution was to convert to seconds, do my math, and then convert back to a TIME.

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)

For a small data set, I would just compute the "seconds" values from each table and compare. So I'd end up joining:

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

against

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

But if there are a lot of rows in the reference table (t) or there were an index on t.ss that could speed up the query, we'd have to get time values back. SEC_TO_TIME solves that aspect.

So, here's an example rounding to the nearest half hour (hence the 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)

Assuming that ss.t is already rounded:

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')  )