MySQL - SYSDATE() Function

The MySQL SYSDATE() 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.

This function is similar to the NOW() function but, SYSDATE() returns the time at which it executes and NOW() returns the time it began to execute. i.e. if we use NOW() within a stored function it returns the start of execution time of the stored procedure.

Syntax

Following is the syntax of MySQL SYSDATE() function −

SYSDATE();

Parameters

This method does not accept any parameters.

Return value

This function returns the DATETIME value representing the current date and time according to the system clock.

Example

In the following example, we are using the MySQL SYSDATE() function to get the current date and time value −

SELECT SYSDATE() As Result;

Output

This will produce the following result −

Result
2023-11-16 14:31:10

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 SYSDATE()+0 As Result;

Output

Following is the output −

Result
20231116143110

Example

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

SELECT SYSDATE()+12 As Result;

Output

Following is the output −

Result
20231116143122

Example

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

SELECT SYSDATE()-12 As Result;

Output

Following is the output −

Result
20231116143098

Example

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

SELECT SYSDATE(3) As Result;

Output

Following is the output −

Result
2023-11-16 14:31:10.162

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 SYSDATE() function to calculate the difference in days between the "DATE" column and the current date and time −

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

Output

The output is displayed as follows −

OID DATE Day_Difference
102 2009-10-08 00:00:00 5152
100 2009-10-08 00:00:00 5152
101 2009-11-20 00:00:00 5109
103 2008-05-20 00:00:00 5658
Advertisements