How to Get the Current Date and Time in MySQL

Problem:

You’d like to get the current date and time for a MySQL database.

Solution:

We’ll use one of two functions, CURRENT_TIMESTAMP or NOW(), to get the current date and time.

SELECT CURRENT_TIMESTAMP ;

Here’s the result of the query:

2019-08-15 11:13:17

Discussion:

The CURRENT_TIMESTAMP function in the MySQL database returns the current date and time (i.e. the time for the machine running that instance of MySQL). It is given as a value in the 'YYYY-MM-DD hh:mm:ss' format.

This function does not require brackets. However, you can use brackets to specify a higher precision time. Simply place an integer from 1 to 6 within the brackets – this determines the number of fractional seconds to include after the decimal. A 1 denotes only one place after the decimal, a 3 denotes three places, etc. Here’s an example:

SELECT CURRENT_TIMESTAMP(6) ;

This returns the date and time with six fractional seconds, like so:

2019-08-27 12:08:56.146153

This result contains a 6-digit fractional second because we put 6 as the optional argument in the CURRENT_TIMESTAMP function.

The NOW() function is similar to the CURRENT_TIMESTAMP function and returns the same result. The difference is that CURRENT_TIMESTAMP is the SQL standard function, while NOW() is specific to MySQL. Let’s see an example with NOW():

SELECT NOW() ;

Here’s the result of the query:

2019-08-27 12:18:55

Unlike CURRENT_TIMESTAMP, NOW() requires brackets. You can leave the brackets empty, which returns the date and time without fractional seconds. If you want to get a more accurate result, use an integer from 1 to 6 as the optional argument (as with CURRENT_TIMESTAMP). It will return the number of fractional seconds you specify.