What is the difference between NOW() and SYSDATE() in SQL?
In SQL, both the NOW() and SYSDATE() functions return the current time and date, but there is a bit of difference between both functions.
The NOW() function
The NOW() function returns the date and time when the statement is executed. If the NOW() function is called twice in a statement, it returns the same date and time when the statement gets executed.
Let’s look at the following query:
SELECT NOW() AS 'NOW() BEFORE SLEEP()',SLEEP(5) AS '',NOW() AS 'NOW() AFTER SLEEP()';
As we can see, the NOW() function returns the same time before and after the SLEEP() function.
The SYSDATE() function
The SYSDATE() function returns the current date and time when it is called. If it’s called twice in a statement, it returns the exact date and time of the system at that function call.
Let’s consider the following query:
SELECT SYSDATE() AS 'SYSDATE() BEFORE',SLEEP(5) AS '',SYSDATE() AS 'SYSDATE() AFTER';
We can see that in the output of the above query, there is a difference of 5 seconds in the timestamp output of the SYSDATE() function.
Difference between the NOW() and SYSDATE() functions
The following illustration shows the difference between the NOW() and SYSDATE() functions:
Let’s call the NOW() and SYSDATE() functions in one query and observe the difference in the following playground:
SELECT SLEEP(5) AS '',SYSDATE() AS 'SYSDATE() AFTER SLEEP',NOW() AS 'NOW() AFTER SLEEP';
In the above query, we’ve called the SYSDATE() and NOW() functions after the SLEEP() call of 5 seconds. We can see the difference of 5 seconds in the output of the NOW() and SYSDATE() functions. This is because the NOW() returns the time of the SELECT statement execution and SYSDATE() returns the current time.
Conclusion
By looking at these examples, we can conclude the following:
- The
SYSDATE()function returns the current system time at the function call. - In a statement, the
NOW()function returns the time of statement execution.
Free Resources
