MySQL - NOW() Function

The MySQL NOW() function is used to get the current date and time value. The resultant value is a string or a numerical value based on the context and, the value returned will be in the 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss format.

The current date and time values are local to a system due to different time zones. For instance, if a system's locality is in India, the time zone followed by this function would be Indian Standard Time.

Syntax

Following is the syntax of MySQL NOW() function −

NOW();

Parameters

This method does not accept any parameters.

Return value

This function returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS'.

Example

Following example demonstrates the usage of the MySQL NOW() function −

SELECT NOW() As Result;

Output

This will produce the following result −

Result
2023-11-15 11:05:51

Example

Here, we are adding 0 to the current date and time value. The "+0" serves as a simple way to convert the date and time to a numeric representation −

SELECT NOW()+0 As Result;

Output

Following is the output −

Result
20231115110551

Example

We can add seconds to the current time stamp as shown below −

SELECT NOW()+12  As Result;

Output

Following is the output −

Result
20231115110563

Example

We can also subtract the desired number of seconds from the current time using this function −

SELECT NOW()-12 As Result;

Output

Following is the output −

Result
20231115110539

Example

This function accepts an optional argument i.e. fsp, using this we can specify the number of digits you need after the fraction for seconds.

SELECT NOW(3) As Result;

Output

Following is the output −

Result
2023-11-15 11:05:51.166

Example

In this example, we have created a table named ORDERS using the following CREATE TABLE query −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Here, we are using the MySQL NOW() function to calculate the difference in days between the "DATE" column and the current date and time −

SELECT OID, DATE, TIMESTAMPDIFF(DAY, DATE, NOW())
As Day_Difference FROM ORDERS;

Output

The output is displayed as follows −

OID DATE Day_Difference
102 2009-10-08 00:00:00 5151
100 2009-10-08 00:00:00 5151
101 2009-11-20 00:00:00 5108
103 2008-05-20 00:00:00 5657
Advertisements