SYSDATE Function - Oracle to MySQL Migration

In Oracle, the SYSDATE function returns the current date and time for the database server's host operating system at the statement execution time.

In MySQL, you can use the NOW() and SYSDATE() functions (parentheses are required), which also include the time. They both return the value in the session time zone.

Note that MySQL NOW() returns the current date and time at the statement execution time, while SYSDATE() returns the time at which the function executes.

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- Get the current date and time
  SELECT SYSDATE FROM dual;
  /* 2025-04-09 19:24:55 */

MySQL:

  -- Get the current date and time
  SELECT NOW();
  /* 2025-04-09 19:24:55 */

In Oracle, SYSDATE returns the date and time in the time zone of the database instance's operating system:

Oracle:

  ALTER SESSION SET TIME_ZONE = '-05:00';
 
  -- Get the session and database time zones
  SELECT SESSIONTIMEZONE, DBTIMEZONE FROM dual;
  /* -05:00   +00:00 */
 
  -- SYSDATE, SYSTIMESTAMP uses database OS time zone, while CURRENT_DATE uses session time zone
  SELECT SYSDATE, SYSTIMESTAMP, CURRENT_DATE FROM dual;

Result:

You can see that SYSDATE (the time zone can be seen in the SYSTIMESTAMP result) returned the current time in UTC +01:00 time zone (database OS time zone), even though the session time zone was set to UTC -05:00 and the database time zone is UTC +00:00.

Meanwhile, CURRENT_DATE returned the value in the session time zone UTC -05:00.

In MySQL, NOW() and SYSDATE() return the date and time in the session time zone.

By default, the MySQL session time zone is set to SYSTEM, meaning it is the same as the database instance time zone.

MySQL:

  -- Get system and session time zone
  SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
  /* SYSTEM     SYSTEM */
 
  -- If session time zone is SYSTEM, you can define the database server time zone
  SELECT NOW(), TIMEDIFF(NOW(), UTC_TIMESTAMP);
  /* 2025-04-09 19:29:05      01:00:00 */

If the session time zone is changed, you can use the CONVERT_TZ function to get the current date and time in the database server time zone:

MySQL:

  -- Change the session time zone
  SET TIME_ZONE = '-05:00';
 
  -- NOW() now returns time in the session time zone
  SELECT NOW(), TIMEDIFF(NOW(), UTC_TIMESTAMP);
  /* 2025-04-09 13:29:05      -05:00:00 */
 
  -- Get the current date and time in the database server time zone
  SELECT CONVERT_TZ(NOW(), @@SESSION.time_zone, 'SYSTEM'); 
  /* 2025-04-09 19:29:05 */

Oracle returns the current date and time at the statement execution time:

Oracle:

  WITH FUNCTION SLEEP_SYSDATE(i NUMBER)
  RETURN DATE IS
  BEGIN
    DBMS_SESSION.SLEEP(i);
    RETURN SYSDATE;
  END;
  SELECT SYSDATE, SLEEP_SYSDATE(3), SYSDATE FROM dual
  UNION ALL
  SELECT SYSDATE, SLEEP_SYSDATE(3), SYSDATE FROM dual;
  /

Result:

You can observe that all four SYSDATE calls in the query returned the same result, whereas two SYSDATE calls in the user-defined function returned different results (after a delay).

In MySQL, NOW() returns the current date and time at the statement execution time, while SYSDATE() returns the time at which the function executes.

MySQL:

  -- Get the current date and time multiple times with a delay in the same query
  SELECT NOW(), SYSDATE(), SLEEP(3), NOW(), SYSDATE();

Result:

You can observe that NOW() returned the same value, while SYSDATE() returned different values.

Oracle allows you to use SYSDATE in a column default:

Oracle:

  CREATE TABLE t1 (c1 DATE DEFAULT SYSDATE);
  /* Table created. */

In MySQL, you can use NOW() and SYSDATE() in a column default, but SYSDATE() must be enclosed in parentheses:

MySQL:

  -- SYSDATE() is not allowed in DEFAULT unless enclosed in parentheses ()
  CREATE TABLE t1 (c1 DATETIME DEFAULT SYSDATE());
  /* ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
      corresponds to your MySQL server version for the right syntax to use near 'SYSDATE())' at line 1 */
 
  -- SYSDATE() enclosed with parentheses ()
  CREATE TABLE t1 (c1 DATETIME DEFAULT (SYSDATE()));
  /* Query OK, 0 rows affected */
 
  -- NOW() in DEFAULT   
  CREATE TABLE t1 (c1 DATETIME DEFAULT NOW());
  /* Query OK, 0 rows affected */

For more information, see Oracle to MySQL Migration.