MySQL NOW() Function

November 10, 2022 | by Arround The Web | No comments

This post will discuss one of MySQL’s most popular and useful date and time functions.

The NOW() function allows us to get the current date and time in the running host in the format: YYYY-MM-DD HH:MM:DD or YYYYMMDDHHMMSS. The function can return the value as string type or numerical value depending on the usage.

Function Syntax

Below is the syntax of the NOW()function in MySQL:

NOW([fsp])

The function accepts the FSP parameter, which determines the fractional seconds precision. This optional parameter specifies the number of values displayed in the seconds section.

Unlike the SYSDATE() function, the NOW() function returns a constant value denoting the time the statement began the execution.

Example 1: Basic Function Usage

The following shows the basic usage of the NOW() function.

select now() as c_time;

This should return the current date and time as:

c_time |
-------------------+
2022-10-24 14:52:05|

Example 2: Using the NOW() Function With Calculation

You can use the NOW() function with simple calculations. For example, the following statements add and remove various units to the current date and time.

select (now() - interval 3 hours) '3 hours before',
(now() + interval 3 hours) '3 hours after',
(now() + interval 3 days) '3 days later',
(now() - interval 5 days) '5 days before';

This should return the values as follows:

Example 3: NOW() vs SYSDATE() Functions

The following example shows you the difference between the NOW() and SYSDATE() functions:

SELECT NOW(), SLEEP(2), NOW();
SELECT SYSDATE(), SLEEP(2), SYSDATE();

ABOVE: NOW() Function:

Above: SYSDATE() Function:

The NOW() function returns the date and time the statement begins execution, while the SYSDATE() function returns the date and time after execution.

Example 4: Using NOW() function as the Default Value

create a table sample(
id int not null auto_increment primary key,
time datetime not null default now()
);

You can then run the insert statement multiple times to insert random values:

insert into sample values();

Resulting table:

select * from SAMPLE S;

Output:

Conclusion

In this post, we discussed how to use MySQL’s NOW() function to fetch the current date and time when the system begins execution. Several examples highlighted the difference between the NOW() function and the SYSDATE() function.

Source: linuxhint.com