How to use the MySQL NOW() function

In this article, we will learn how to use the MySQL NOW() function, which returns the current date and time as a datetime value.

Posted on

In this article, we will learn how to use the MySQL NOW() function, which returns the current date and time as a datetime value. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with dates and times.

Syntax

The syntax of the NOW() function is as follows:

NOW()

The NOW() function does not take any parameters. It returns the current date and time as a datetime value, in the format of ‘YYYY-MM-DD HH:MM:SS’. The value returned by the NOW() function depends on the system time zone of the MySQL server.

Examples

Let’s see some examples of how to use the NOW() function in MySQL.

Example 1: Get the current date and time

We can use the NOW() function to get the current date and time as a datetime value. For example:

SELECT NOW() AS current_datetime;

This query will return the current date and time as a datetime value. For example, if the system time zone of the MySQL server is GMT+8, and the current date and time is 2023-12-15 10:02:34, the query will return ‘2023-12-15 10:02:34’.

Example 2: Get the current date

We can use the NOW() function with the DATE() function, which returns the date part of a datetime value, to get the current date as a date value. For example:

SELECT DATE(NOW()) AS current_date;

This query will return the current date as a date value, in the format of ‘YYYY-MM-DD’. For example, if the current date and time is 2023-12-15 10:02:34, the query will return ‘2023-12-15’.

Example 3: Get the current time

We can use the NOW() function with the TIME() function, which returns the time part of a datetime value, to get the current time as a time value. For example:

SELECT TIME(NOW()) AS current_time;

This query will return the current time as a time value, in the format of ‘HH:MM:SS’. For example, if the current date and time is 2023-12-15 10:02:34, the query will return ‘10:02:34’.

Example 4: Get the current year, month, and day

We can use the NOW() function with the YEAR(), MONTH(), and DAY() functions, which return the year, month, and day of a date or datetime value, respectively, to get the current year, month, and day as integer values. For example:

SELECT YEAR(NOW()) AS current_year, MONTH(NOW()) AS current_month, DAY(NOW()) AS current_day;

This query will return the current year, month, and day as integer values. For example, if the current date and time is 2023-12-15 10:02:34, the query will return 2023, 12, and 15, respectively.

Example 5: Get the current hour, minute, and second

We can use the NOW() function with the HOUR(), MINUTE(), and SECOND() functions, which return the hour, minute, and second of a time or datetime value, respectively, to get the current hour, minute, and second as integer values. For example:

SELECT HOUR(NOW()) AS current_hour, MINUTE(NOW()) AS current_minute, SECOND(NOW()) AS current_second;

This query will return the current hour, minute, and second as integer values. For example, if the current date and time is 2023-12-15 10:02:34, the query will return 10, 2, and 34, respectively.

There are some other functions that are related to the NOW() function, and can be useful for working with dates and times. Here are some of them:

  • CURDATE(): This function returns the current date as a date value. For example, CURDATE() returns ‘2023-12-15’ if the current date is 2023-12-15.
  • CURTIME(): This function returns the current time as a time value. For example, CURTIME() returns ‘10:02:34’ if the current time is 10:02:34.
  • SYSDATE(): This function returns the current date and time as a datetime value, similar to the NOW() function. However, the SYSDATE() function is not affected by the SET TIMESTAMP statement, while the NOW() function is. For example, SYSDATE() returns ‘2023-12-15 10:02:34’ if the current date and time is 2023-12-15 10:02:34, regardless of the value set by the SET TIMESTAMP statement.
  • UTC_DATE(): This function returns the current UTC date as a date value. For example, UTC_DATE() returns ‘2023-12-15’ if the current UTC date is 2023-12-15.
  • UTC_TIME(): This function returns the current UTC time as a time value. For example, UTC_TIME() returns ‘02:02:34’ if the current UTC time is 02:02:34.
  • UTC_TIMESTAMP(): This function returns the current UTC date and time as a datetime value. For example, UTC_TIMESTAMP() returns ‘2023-12-15 02:02:34’ if the current UTC date and time is 2023-12-15 02:02:34.

Conclusion

In this article, we learned how to use the MySQL NOW() function, which returns the current date and time as a datetime value. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with dates and times.