Updated May 16, 2023
Introduction to MySQL SYSDATE()
In MySQL, we can find a range of Date and Time functions from which we can choose one for accessing the up-to-date date/time value. Most of these date and time functions are found to be synonyms for each one. From those, MySQL SYSDATE() function provides the current date/time value when executed.
However, MySQL SYSDATE() function and MySQL NOW() function results give the present date/time seem similar, but their behaviors are slightly different. MySQL SYSDATE() function returns the strict date and time it is executed on the server. Still, the MySQL NOW() function provides a persistent date and time that signifies the time value at which the query or procedure was initiated to be executed.
Syntax
The basic simple syntax structure for SYSDATE() function in MySQL is as follows:
SELECT SYSDATE();Though it is a function, we need not define any parameters for the function. But we can still add an optional parameter, ‘fsp’, which the function accepts. This parameter allows us to specify whether the output should include a fractional part of seconds accuracy ranging from 0 to 6.
SELECT SYSDATE(fsp);But its format in result output will differ if used in varying contexts. Suppose,
- If the context is a string, the SYSDATE() function returns the current date in the format – YYYY-MM-DD HH:MM:SS.
- If the context is numeric, the SYSDATE() function returns the current date in the format – YYYYMMDDHHMMSS.
How does the MySQL SYSDATE() Function work?
SYSDATE() function is helpful to generate the current date and time, but it also has some caveats to know before we use it as it provides the time value of the execution period.
In MySQL, the result of SYSDATE() and NOW() functions both depend upon the execution time at which the query statement started and was executed. This slightly makes a big dissimilarity in both the DATE functions in MySQL.
You can observe the difference between these two functions through the example provided below:
Suppose, we have SYSDATE() and NOW() query and the result as:
SELECT NOW(), SYSDATE();Output:
From the output above, the result value seems identical for both, representing the current date and time of its execution. i.e., no difference.
But, again, if we provide a gap between the query execution, the result might be hampered. For this, let us use the SLEEP() function, which will pause the query statement execution time in seconds as specified in the argument.
SELECT NOW(), SLEEP(6), NOW();Output:
In the above example, the NOW() while running was paused for some time, i.e., 6s, but as the NOW() shows the steady time at which the command was started, the time does not affect it.
Now, let us follow the same for SYSDATE() function and watch the difference using the query as follows:
SELECT SYSDATE(), SLEEP(6), SYSDATE();Output:
As the output displayed above makes it clear, the SYSDATE() function takes the time of the current execution after a system sleep or pause of 6 seconds in the execution period. In the same statement, the SYSDATE() shows values that vary in time intervals. In contrast, the last one adds 6s to its result set timestamp as it needs to show only the current time the query is finished processing the statement on the server.
Indeed, the SYSDATE function is classified as non-deterministic in MySQL. Consequently, it cannot be utilized for index operations or calculations involving terms that depend on it. When executing queries on table columns with the DATETIME data type, using the SYSDATE() function may potentially impact multiple or even all rows to evaluate the result, bypassing the benefits of indexes. On the other hand, if you employ NOW() instead, the EXPLAIN clause statement can reveal that the search or calculation process only affects a small number of rows, resulting in more efficient retrieval of the current date and time outputs.
So, it is important to notice the usage of the SYSDATE() function twice before considering it for use in the database, as it can have some negative implications.
Also, the SET TIMESTAMP query in MYSQL does not affect the output value of the SYSDATE() function but may affect the output of the NOW() function.
This difference may cause some problems while performing the queries, but we have a better option to avoid this. We can make SYSDATE() as an alias for MySQL NOW() function so that it shows similar behavior as NOW().
To address the dissimilarity between SYSDATE() and NOW() in MySQL, you can utilize the –sysdate-is-now MySQL server command option. Enabling this option ensures that the results obtained from SYSDATE() and NOW() consistently provide accurate date and time values whenever you execute the query statement.
This alternative way can help work on the enslaved person and the enslaver. One possible solution to address the potential replication safety issue with the SYSDATE() function in MySQL when using statement-based binary logging is to switch to row-based binary logging on the server.
Examples of implementing the SYSDATE() function in MySQL
Let us take the following examples to understand better:
Example #1 – Simple Example using SYSDATE()
We use SYSDATE() function in combination with to SELECT SQL statement to run the command in MySQL as follows:
Code:
SELECT SYSDATE();Output:
Example #2 – Fractional Seconds Precision
We are considering the SYSDATE() with fsp parameter value to return the result.
Code:
SELECT SYSDATE(4);Output:
Example #3 – Numeric Context
This query will execute the result in the format specified as – YYYYMMDDHHMMSS.
Code:
SELECT SYSDATE() + 0;Output:
Here, in the function, you can even apply a nonzero value to add or subtract from the returning datetime value. View the below query with output:
Code:
SELECT SYSDATE() + 2;Output:
Conclusion
- SYSDATE() function provides the current datetime of query execution while the NOW() function returns the datetime at which the MySQL function triggering statement started.
- Thus, the SYSDATE() function shows a self-executed interval of the function itself but not the query completing time on the server.
- The usage of SYSDATE() and NOW() together can result in different execution values for the timestamp due to this phenomenon.
Recommended Articles
We hope that this EDUCBA information on “MySQL SYSDATE()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
