SYSDATE Function - Oracle to MariaDB 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 MariaDB, 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 MariaDB 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 */

MariaDB:

  -- 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 MariaDB, NOW() and SYSDATE() return the date and time in the session time zone.

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

MariaDB:

  -- 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:

MariaDB:

  -- 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 MariaDB, NOW() returns the current date and time at the statement execution time, while SYSDATE() returns the time at which the function executes.

MariaDB:

  -- 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.

For more information, see Oracle to MariaDB Migration.