Introduction
Working with time and dates is a fundamental aspect of database management, especially for applications that require time-based records or scheduling. MySQL 8 provides various functions for dealing with dates and times, among which CURTIME() and CURRENT_TIME() are particularly useful for retrieving the current time. In this tutorial, we’ll explain how to use CURTIME() and CURRENT_TIME() in MySQL 8, demonstrating their usage through examples ranging from the basic to the more advanced.
Understanding CURTIME() and CURRENT_TIME()
Both CURTIME() and CURRENT_TIME() are functions that return the current time. They can be used interchangeably as they essentially perform the same operation. They produce output in ‘HH:MM:SS’ format or ‘HHMMSS.uuuuuu’ format when fractional seconds are requested.
Basic Example
SELECT CURTIME();
-- Output: 10:15:00
SELECT CURRENT_TIME();
-- Output: 10:15:00
These functions grab the time from your database system’s clock at the point in time the query is executed.
Fetching time with fractional seconds
SELECT CURTIME(3);
-- Output: 10:15:00.123
SELECT CURRENT_TIME(3);
-- Output: 10:15:00.123
In the examples above, the parameter ‘3’ denotes the precision of the fractional seconds.
Working with CURTIME() and CURRENT_TIME() in Table Context
These functions become more useful when combined with queries on a table. Consider a basic table named ‘task_log’ with columns ‘task_id’, ‘task_name’, and ‘task_time’.
CREATE TABLE task_log (
task_id INT AUTO_INCREMENT PRIMARY KEY,
task_name VARCHAR(255) NOT NULL,
task_time TIME NOT NULL DEFAULT CURTIME()
);
Now, whenever a new task record is inserted without specifying the ‘task_time’, the CURTIME() function will automatically populate the field with the current time.
INSERT INTO task_log (task_name) VALUES ('Database Backup');
The ‘task_time’ column for the new record ‘Database Backup’ will be set to the time of insertion.
Comparing and Filtering Time
Frequently, queries necessitate filtering rows based on time. We can use CURTIME() or CURRENT_TIME() in a WHERE clause for such operations.
SELECT * FROM task_log WHERE task_time < CURTIME();
This query will retrieve all tasks that were logged before the current time.
Advanced Examples
We can also use CURTIME() and CURRENT_TIME() in more advanced scenarios, such as in joining tables, calculations involving time differences, or scheduled events.
Calculating Time Difference
SELECT task_name, TIMEDIFF(CURTIME(), task_time) AS time_since_task FROM task_log;
The above query will show how much time has passed since each task was logged.
Using CURTIME() with Scheduled Events
CREATE EVENT reminder_event
ON SCHEDULE EVERY 1 HOUR STARTS CONCAT(CURDATE(), ' ', CURTIME())
DO
UPDATE task_log SET notification_sent = TRUE
WHERE task_time < DATE_SUB(CURTIME(), INTERVAL 1 HOUR) AND notification_sent = FALSE;
This scheduled event checks every hour if a task was recorded more than an hour ago but hasn’t had a notification sent for it. If such tasks are found, it marks them accordingly.
Conclusion
This tutorial aimed to explain how to use CURTIME() and CURRENT_TIME() in MySQL 8, starting from basic examples and progressing towards more complex use cases. With the knowledge gained, you can now integrate time-aware features into your MySQL databases seamlessly.
