EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • 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
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL SYSDATE()

MySQL SYSDATE()

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byPriya Pedamkar

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW