Mastering MySQL TIME Data Type

The TIME data type in MySQL is used to store time values (HH:MM:SS format). It is useful for representing durations, time of day, or intervals without any associated date.

1. Understanding TIME Data Type

Feature	Details
Format	`'HH:MM:SS'`
Storage	3 bytes
Range	`'-838:59:59'` to `'838:59:59'`
Default Value	`'00:00:00'` (if NOT NULL is not specified)

✔ Supports negative values for time differences.
✔ Can store values greater than 24 hours, unlike some other databases.

2. Creating a Table with TIME Column

CREATE TABLE schedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_name VARCHAR(100),
    task_time TIME NOT NULL
);

✔ The task_time column must be manually set when inserting records.

3. Inserting TIME Values

INSERT INTO schedules (task_name, task_time) 
VALUES ('Morning Meeting', '08:30:00');

✔ TIME values must be in 'HH:MM:SS' format.

Using SEC_TO_TIME() to insert a computed duration:

INSERT INTO schedules (task_name, task_time) 
VALUES ('Long Task', SEC_TO_TIME(3600)); -- Converts 3600 seconds to '01:00:00'

4. Retrieving and Formatting TIME Values

Fetch all records:

SELECT * FROM schedules;

Format TIME output:

SELECT task_name, TIME_FORMAT(task_time, '%h:%i %p') AS formatted_time 
FROM schedules;

✔ Converts '08:30:00' → '08:30 AM'.

5. Updating TIME Values

Change the task time:

UPDATE schedules 
SET task_time = '10:45:00' 
WHERE task_name = 'Morning Meeting';

✔ Updates the time value.

6. Working with TIME Arithmetic

Adding and Subtracting Time

Add 2 hours to a time:

SELECT ADDTIME('08:30:00', '02:00:00'); -- Returns '10:30:00'

Subtract 30 minutes:

SELECT SUBTIME('08:30:00', '00:30:00'); -- Returns '08:00:00'

Finding Time Differences

Find the time difference between the two times:

SELECT TIMEDIFF('12:00:00', '08:30:00'); -- Returns '03:30:00'

Convert a time into total seconds:

SELECT TIME_TO_SEC('01:30:00'); -- Returns 5400 seconds

7. Comparing TIME Values

Find tasks scheduled after 10 AM:

SELECT * FROM schedules 
WHERE task_time > '10:00:00';

Find all tasks scheduled before now:

SELECT * FROM schedules 
WHERE task_time < CURTIME();

✔ CURTIME() returns the current time.

8. Extracting Parts of a TIME Value

Get the hour:
```
SELECT HOUR(task_time) FROM schedules;
```

Get the minutes:

SELECT MINUTE(task_time) FROM schedules;

Get the seconds:

SELECT SECOND(task_time) FROM schedules;

9. Converting TIME to Other Formats

Convert TIME to hours and minutes:

SELECT TIME_FORMAT(task_time, '%h:%i %p') FROM schedules;

✔ Converts '08:30:00' → '08:30 AM'.

Convert TIME to decimal hours:

SELECT HOUR(task_time) + (MINUTE(task_time) / 60) FROM schedules;

✔ Converts '02:30:00' → 2.5 hours.

10. Summary

TIME stores values from -838:59:59 to 838:59:59.
Use TIME_FORMAT() for custom display formats.
Use ADDTIME() and SUBTIME() for time calculations.
Use TIMEDIFF() to find differences between two TIME values.
Use TIME_TO_SEC() and SEC_TO_TIME() for conversions.

Would you like to see examples of real-world use cases, such as tracking work hours or logging durations? 🚀

Souy Soeng

Mastering MySQL TIME Data Type