MySQL LOCALTIME() and NOW() Functions – Displaying the current date and time

By /
LOCALTIME NOW

In this tutorial, we will study the MySQL LOCALTIME() and NOW() functions. So far we have seen the CURDATE() and the CURTIME() functions which return the current date and current time respectively.

What if we need a function that returns the current date and time together? This is where the LOCALTIME() and NOW() functions come into play. The MySQL LOCALTIME() and NOW() functions return the current date and time value (or local area date and time value) in the YYYY-MM-DD HH:MM:SS format.  

Syntax of MySQL LOCALTIME()

LOCALTIME([fsp])Code language: SQL (Structured Query Language) (sql)

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 NOW()

NOW([fsp])Code language: SQL (Structured Query Language) (sql)

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

Examples of MySQL LOCALTIME() and NOW()

Let us kick things off with some basic examples of the LOCALTIME() and NOW() functions. Below is a query that uses the SELECT statement with the LOCALTIME() function to display the current date and time.

SELECT LOCALTIME();Code language: SQL (Structured Query Language) (sql)

And the output is –

Localtime Basic Example

Similarly, we can do this using the NOW() function as follows.

SELECT NOW();Code language: SQL (Structured Query Language) (sql)

And the output is –

Now Basic Example

MySQL LOCALTIME() and NOW() With Numeric Context

When LOCALTIME() and NOW() are used in a numeric context, they return the output in the format – YYYYMMDDHHMMSS. Let us see how it is with the LOCALTIME() function. The query is –

SELECT LOCALTIME() + 0;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Localtime Numeric Context

Similarly, we can do this using the NOW() function as follows.

SELECT NOW() + 0;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Now Numeric Context

Adding To MySQL LOCALTIME() and NOW()

We can add numbers to the numeric context of the LOCALTIME() and NOW() function results. Let us see this below.

SELECT LOCALTIME() + 0, LOCALTIME() + 2;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Localtime Add Numeric Context

With the NOW() function, the query is –

SELECT NOW() + 0, NOW() + 5;Code language: SQL (Structured Query Language) (sql)

And the output is –

Now Add Numeric Context

Comparison of CURDATE(), LOCALTIME(), NOW() and CURTIME() functions

Let us compare the CURDATE(), LOCALTIME(), NOW() and CURTIME() functions using the below query.

SELECT CURDATE(), LOCALTIME(), NOW(), CURTIME();Code language: SQL (Structured Query Language) (sql)

And the output is –

Localtime Curtime Curdate

You see, the LOCALTIME() and NOW() function’s results are basically the result of CURDATE() concatenated with the result of CURTIME().

Fractional Seconds Precision using LOCALTIME() and NOW()

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 NOW() and LOCALTIME() functions will return the datetime 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 LOCALTIME() function.

SELECT LOCALTIME(3); SELECT LOCALTIME(6);Code language: SQL (Structured Query Language) (sql)

And the output is –

Localtime Fsp

Using the NOW() function, the query is –

SELECT NOW(4); SELECT NOW(6);Code language: SQL (Structured Query Language) (sql)

And the output is –

Now Fsp

Working with Tables

Consider the below Employee table.

Employee Table
Employee Table

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

Suppose all the employees entered the building at the same time on that day. As they are entering the office, you have to enter the date and time in the Employee table.

Let us use MySQL LOCALTIME() function to do this. Since we have to create the CheckInDateTime column and add the current date and time value to it, we will also use the ALTER and UPDATE statements. The queries are –

ALTER TABLE Employee ADD CheckInDateTime datetime; 
UPDATE Employee SET CheckInDateTime=LOCALTIME(); 
SELECT * FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is –

Localtime Table Example1

Similarly, this can also be done using the NOW() function as follows –

UPDATE Employee SET CheckInDateTime=NOW(); 
SELECT * FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is –

Now Table Example2

Conclusion

You will find yourself using the MySQL LOCALTIME() and NOW() functions quite often while working with databases. Displaying and storing the date and the time together has many use cases across various applications and domains.

References