How the CURTIME() function works in Mariadb?

The MariaDB CURTIME() function is used to retrieve the current time of the server’s timezone in HH:MM:SS or HHMMSS.uuuuuu format, depending on whether the function is used with or without arguments. It is commonly utilized in recording time stamps, calculating time differences, and scheduling events within database applications.

Syntax

MariaDB CURTIME() function’s syntax is as follows:

CURTIME([fsp])

fsp is an optional argument that stands for fractional seconds precision. It can range from 0 to 6. If omitted, the default precision is 0.

Examples

Example 1: Retrieve the Current Time

To get the current time without fractional seconds:

SELECT CURTIME();

The output will be the current time:

+-----------+
| CURTIME() |
+-----------+
| 09:26:36  |
+-----------+

Example 2: Retrieve the Current Time with Fractional Seconds

To get the current time with fractional seconds:

SELECT CURTIME(4);

The output will be the current time with fractional seconds:

+---------------+
| CURTIME(4)    |
+---------------+
| 09:26:46.8423 |
+---------------+

Example 3: Calculate Time Elapsed Since Midnight

To calculate the time elapsed since midnight:

SELECT TIMEDIFF(CURTIME(), '00:00:00') AS Time_Elapsed;

The output will show the time elapsed since midnight:

+--------------+
| Time_Elapsed |
+--------------+
| 09:26:54     |
+--------------+

Example 4: Compare Current Time with a Specific Time

To compare the current time with a specific time to check if it is earlier:

SELECT CURTIME() < '12:00:00' AS Is_Morning;

The output will indicate whether the current time is before noon:

+------------+
| Is_Morning |
+------------+
|          1 |
+------------+

Example 5: Use `CURTIME()` in a WHERE Clause

If you need to filter records based on the current time, you can use CURTIME() in the WHERE clause:

DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
    id INT,
    content VARCHAR(255),
    sent_time TIME
);
INSERT INTO messages VALUES (1, 'Hello', '09:00:00'), (2, 'Goodbye', '15:00:00');

SELECT content FROM messages WHERE CURTIME() < sent_time;

The output will display messages sent before the current time:

+---------+
| content |
+---------+
| Goodbye |
+---------+

Here are a few functions related to the MariaDB CURTIME() function:

MariaDB CURRENT_TIME() function is used as a synonym for CURTIME().
MariaDB NOW() function returns the current date and time.
MariaDB SYSDATE() function returns the current date and time, but unlike NOW(), it returns the exact time the function is executed.

Conclusion

The CURTIME() function in MariaDB is a simple yet powerful tool for managing time-related data. It is especially useful for applications that need to record or compare times, schedule tasks, or track durations. By understanding how to use CURTIME() and its related functions, developers can effectively handle time in their database applications, ensuring accurate and timely data management.