MySQL CURTIME() and CURRENT_TIME() – Print Current Time

Curtime

In this tutorial, we will learn about MySQL CURTIME() and CURRENT_TIME() functions. Suppose you are an office manager and you need to keep track of the check-in time of every employee for the day and you want to make a column called CheckIn_Time in your Employee table. The CheckIn_Time column stores the time at which every employee entered the office building for work for that day.

Your job as an office manager is to fill this CheckIn_Time column with the time at which each employee entered the office. For such operations, MySQL provides us with the CURTIME() and CURRENT_TIME() functions. The CURTIME() and CURRENT_TIME() functions are used to return the current time. They do the same thing, the only difference is in their names. Note that CURTIME() and CURRENT_TIME() return the time in the 24-hour format. Let us look at the syntax of them followed by a few examples.


Syntax of MySQL CURTIME()

SELECT CURTIME([fsp]); 

Where ‘fsp’ is an optional parameter that is used to get a fractional seconds precision. We will see more of this in the examples.


Syntax of MySQL CURRENT_TIME()

SELECT CURRENT_TIME([fsp]);

Or,

CURRENT_TIME

Where ‘fsp’ is an optional parameter that is used to get a fractional seconds precision. We will see more of this in the examples. Note that, CURRENT_TIME() can also be used simply as CURRENT_TIME.


Examples of MySQL CURTIME() and CURRENT_TIME()

Let us kick things off with a very basic example. Let us return the current time using the MySQL CURTIME() function. The query for it using the SELECT Statement is as follows – 

SELECT CURTIME(); 

And the output is – 

MySQL Curtime Basic Example

Similarly, we can also use the CURRENT_TIME() function for this. The query is – 

SELECT CURRENT_TIME();

And the output is – 

MySQL Current_Time() Basic Example

MySQL CURTIME() and CURRENT_TIME() In Numeric Context

In the previous example, the output returned by the CURTIME() and MySQL CURRENT_TIME() functions were in the string context. Let us see how we can return the output for these functions in a numeric context. The numeric context output just eliminates the colon between the hour, minute, and second values and returns the time in the format – HHMMSS. The query with CURTIME() is – 

SELECT CURTIME() + 0;

And the output is – 

Curtime Numeric

Similarly, using MySQL CURRENT_TIME(), the query is – 

SELECT CURRENT_TIME() + 0;

And the output is – 

Current Time Numeric

MySQL CURTIME() vs CURRENT_TIME()

As I mentioned before in the syntax section, CURTIME() and both syntaxes of CURRENT_TIME() do the same thing – return the current time. Let us demonstrate this using the below query.

SELECT CURRENT_TIME, CURRENT_TIME(), CURTIME();

And the output is – 

Curtime Vs Currenttime

Fractional Seconds Precision using CURTIME() and CURRENT_TIME()

Let us use that optional parameter ‘fsp’ now and see what it does. The optional ‘fsp’ argument is used in cases where we want to know or use the time’s fractional seconds precision. If we pass the ‘fsp’ parameter then the CURTIME() and CURRENT_TIME() will return the time value that will include fractional seconds up to the number provided. You can specify a ‘fsp’ value between 0 and 6. Let us see an example of this using the CURTIME() function.

SELECT CURTIME(5);

And the output is – 

Curtime Fractional Seconds Precision

As you can see, we get a time with 5 fractional seconds. Similarly, we can use the MySQL CURRENT_TIME() function. This time let us find the time value with 6 fractional seconds. The query is – 

SELECT CURRENT_TIME(6);

And the output is – 

Current Time Fractional Seconds Precision

Working with Tables

Consider the below Employee table.

Weekday Employee Table
Employee Table

Now refer to the problem we saw in the beginning. Suppose you are an office manager and you need to keep track of the check-in time of every employee for the day and you want to make a column called CheckIn_Time in an Employee table. The CheckIn_Time column stores the time at which every employee entered the office building for work for that day.

Your job as an office manager is to fill this CheckIn_Time column with the time at which each employee entered the office. Suppose all the employees entered the building at the same time. I know this is a best case scenario which never happens in real life otherwise everyone will get the employee of the month award. Anyway, as they are entering the office, you have to enter the time in the Employee table.

By now you know how to go about this and you can get it done using either the MySQL CURTIME() or MySQL CURRENT_TIME() functions.

Let us use MySQL CURTIME() to do this. The query gets more concise when we use the CURTIME() function. Since we have to create the CheckIn_Time column and add the current time value to it, we will also use the ALTER and UPDATE statements. The queries are – 

ALTER TABLE Employee ADD CheckIn_Time time; 
UPDATE Employee SET CheckIn_Time=CURTIME(); 
SELECT * FROM Employee;

We use the ALTER statement to create a column named CheckIn_Time of the type time. We use the UPDATE statement to set all values in it to the current time using the CURTIME() function and finally, we use the SELECT statement to display the newly updated table. The output is – 

Curtime Table Example

Conclusion

You will find yourself using the MySQL CURTIME() and MySQL CURRENT_TIME() functions quite often while working with databases. They have many applications – one of the most important ones is while storing and displaying the timestamp that is generated after any time-sensitive activity. 


References