MySQL has plenty of date-time functions like MySQL SYSDATE(), NOW(), CURDATE(). All of them are different from each other in some manner; that’s why those all exist! In this tutorial, we will go through the MySQL SYSDATE() function with some nice and clear examples.
The SYSDATE() function is used to get the date and time at which it executes. Now, you might be wondering how the SYSDATE() function is different from other date-time functions like NOW(), right?
SYSDATE() returns a date-time at which it gets executed and not when the query starts executing. Whereas, NOW() function returns a constant time when a query or statement begins to execute. Think of a procedure or a stored function where multiple statements are there. In that case, the NOW() function will return the time when your procedure or stored function starts to execute, but SYSDATE() will return the time when the function itself begins to execute.
Looks confusing?
We will see a few examples to understand it better. Let’s start first with syntax.
Syntax of MySQL SYSDATE()
The basic syntax for the SYSDATE() function is:
SELECT SYSDATE();Code language: SQL (Structured Query Language) (sql)
The SYSDATE() function doesn’t need a parameter to be passed in most cases. However, you can have an optional parameter “fsp” (fraction second precision), which appends the fraction of second to the result, and the parameter value arrays between 0 to 6. Check the syntax below.
SELECT SYSDATE(fsp);Code language: SQL (Structured Query Language) (sql)As stated above, “fsp” ranges from 0 to 6. The parameter determines how many decimal places of the fraction of second should be displayed. Check the examples below.
SELECT SYSDATE(1);Code language: SQL (Structured Query Language) (sql)
SELECT SYSDATE(6);Code language: SQL (Structured Query Language) (sql)
You may also choose what the output type of function should be. If the context of the query is a string, you will get a date-time output in the format of YYYY-MM-DD HH:MM:SS, whereas if the context is numeric, it would be formatted as YYYYMMDD HHMMSS. Let’s see a simple example.
Examples of MySQL SYSDATE() function
As stated earlier, there can be two types of output of the SYSDATE() function, either a string or a numeric. We have seen the date-time output in string format already. Let’s see now how we can get the SYSDATE() output in numeric form.
SELECT SYSDATE()+0;Code language: SQL (Structured Query Language) (sql)
Here, we have simply added 0 to the SYSDATE() function to make it a numeric value and give us output in numeric form. If you are getting confused with the output, let me explain it quickly.
The numeric output is exactly the same as a string output, but without colons and hyphens. That is why the output looks like a random string of digits at first glance.
Now, let’s see how SYSDATE() is different from NOW() function with examples. We will go through some simple tricks instead of using procedures or stored functions to understand the difference between these two functions.
First, let’s execute SYSDATE() and NOW() functions in a single statement and check the result.
SELECT SYSDATE(), NOW();Code language: SQL (Structured Query Language) (sql)
You can see, both functions return the same date-time as expected. You will not find any difference between the two outputs because the query gets executed in a fraction of seconds. So, the trick here is, we are going to use MySQL SLEEP() function to halt the query execution for some time and then check how these two functions behave.
So, we will use the SLEEP() function with NOW() in a single query and see the result.
SELECT NOW(), SLEEP(), NOW();Code language: SQL (Structured Query Language) (sql)
As you can see, even if there is a five-second gap between execution of NOW() functions, both return exact same time.
Because NOW() functions stores the date-time as soon as query or procedure starts executing and displays the respective output.
However, if we perform the same for the SYSDATE() function, the output would be different. Check the example below.
SELECT SYSDATE(), SLEEP(5), SYSDATE();Code language: SQL (Structured Query Language) (sql)
Interesting, right? As explained in the first paragraph, the SYSDATE() function returns the time of its own execution unlike NOW(). That is why we get two distinct outputs.
Conclusion
Here we have studied the MySQL SYSDATE() which is an interesting function. However, we encourage you to use the NOW() function to deal with the date-time functionality in your database tables. Though the SYSDATE() function is not used widely for getting date-time, you may play around with it and apply it in your project depending on different use-cases.
References
MySQL official documentation for SYSDATE() function.