EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL SYSDATE()
 

MySQL SYSDATE()

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated May 16, 2023

MySQL SYSDATE()

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

MySQL SYSDATE() output 1

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:

MySQL SYSDATE() output 2

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:

MySQL SYSDATE() output 3

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:

MySQL SYSDATE() output 4

Example #2 – Fractional Seconds Precision

We are considering the SYSDATE() with fsp parameter value to return the result.

Code:

SELECT SYSDATE(4);

Output:

output 5

Example #3 – Numeric Context

This query will execute the result in the format specified as – YYYYMMDDHHMMSS.

Code:

SELECT SYSDATE() + 0;

Output:

output 6

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:

output 7

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.

  1. MySQL Constraints
  2. Working on MySQL Self Join
  3. MySQL Timestamp
  4. IF Statement in MySQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW