Using Time datatype in MySQL without seconds

Question

I'm trying to store a 12/24hr (ie; 00:00) clock time in a MySQL database. At the moment I am using the time datatype. This works ok but it insists on adding the seconds to the column. So you enter 09:20 and it is stored as 09:20:00. Is there any way I can limit it in MySQL to just 00:00?

Answer 1

That doesn't look possible. The TIME data type is defined to represent the time of the day (or elapsed time) with a 1 second resolution. However, you can always use the DATE_FORMAT() function to format your field as HH:MM in a SELECT query:

SELECT DATE_FORMAT(NOW(), '%k:%i');
+-----------------------------+
| DATE_FORMAT(NOW(), '%k:%i') |
+-----------------------------+
| 4:09                        |
+-----------------------------+
1 row in set (0.00 sec)


SELECT DATE_FORMAT(NOW(), '%H:%i');
+-----------------------------+
| DATE_FORMAT(NOW(), '%H:%i') |
+-----------------------------+
| 04:09                       |
+-----------------------------+
1 row in set (0.00 sec)

Answer 2

The TIME column type does not accept any parameter or modifier to define range or precision. You can, however, omit seconds on insert if you are careful:

Be careful about assigning abbreviated values to a TIME column. MySQL interprets abbreviated TIME values with colons as time of the day. That is, '11:12' means '11:12:00', not '00:11:12'. MySQL interprets abbreviated values without colons using the assumption that the two rightmost digits represent seconds (that is, as elapsed time rather than as time of day). For example, you might think of '1112' and 1112 as meaning '11:12:00' (12 minutes after 11 o'clock), but MySQL interprets them as '00:11:12' (11 minutes, 12 seconds). Similarly, '12' and 12 are interpreted as '00:00:12'.

CREATE TABLE example (
    example_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    elapsed TIME NOT NULL,
    PRIMARY KEY (example_id)
);
INSERT INTO example (elapsed) VALUES ('123:45:00'), ('123:45');
SELECT * FROM example;

+------------+-----------+
| example_id | elapsed   |
+------------+-----------+
|          1 | 123:45:00 |
|          2 | 123:45:00 |
+------------+-----------+

... and you can remove them on read (if necessary) by applying a proper TIME_FORMAT(), noting that:

If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12.

INSERT INTO example (elapsed) VALUES ('2:00');
SELECT example_id, TIME_FORMAT(elapsed, '%k:%i') AS elapsed
FROM example;

+------------+---------+
| example_id | elapsed |
+------------+---------+
|          1 | 123:45  |
|          2 | 123:45  |
|          3 | 2:00    |
+------------+---------+

Since MySQL/5.7.5 you can also use a generated column to get a display value automatically:

-- Completely untested, I don't have 5.7 yet
CREATE TABLE example (
    example_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    elapsed TIME NOT NULL,
    -- Size to accomodate for '-838:59:59'
    elapsed_display VARCHAR(10) AS (TIME_FORMAT(elapsed, '%k:%i')) VIRTUAL NOT NULL,
    PRIMARY KEY (example_id)
);