Datetime Arithmetic - Oracle to MySQL Migration

In Oracle, you can use the + and - operators with DATE (includes time) and TIMESTAMP values i.e. you can add or subtract the specified number of days and fractions (hours, minutes etc.) of the day.

In MySQL, you have to use INTERVAL expressions and functions.

When used with an integer number, the + and - operators add or subtract whole days:

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- Add 1 day
  SELECT TO_DATE('2025-05-29 13:33:31') + 1 FROM dual;
  /* 2025-05-30 13:33:31 */
 
  -- Subtract 1 day
  SELECT SYSDATE - 1 FROM dual;
  /* 2025-05-28 13:33:31 */
 
  -- Using TIMESTAMP value 
  SELECT SYSTIMESTAMP - 1 FROM dual;
  /* 2025-05-28 13:33:31 */

MySQL:

  -- Add 1 day 
  SELECT DATE_ADD('2025-05-29 13:33:31', INTERVAL 1 DAY);
  /* 2025-05-30 13:33:31 */
 
   -- Subtract 1 day
  SELECT DATE_ADD(SYSDATE(), INTERVAL - 1 DAY);
  /* 2025-05-28 13:33:31 */
 
  -- Trying with an integer
  SELECT NOW() - 1;
  /* 20250529133330 */
 
  -- Using INTERVAL expression
  SELECT NOW() - INTERVAL '1' DAY;
  /* 2025-05-28 13:33:31 */

Note that while MySQL allows adding or subtracting integers from datetime values, MySQL implicitly converts datetime values to numbers and then performs operations on numeric values (not datetime) producing a numeric result:

MySQL:

  -- Converts to 20250528133331 and adds 1
  SELECT CAST('2025-05-28 13:33:31' AS DATETIME) + 1;
  /* 20250528133332 */
 
  -- Converts to 20250528133331 and adds 1.1
  SELECT CAST('2025-05-28 13:33:31' AS DATETIME) + 1.1;
  /*  20250528133332.1 */

When used with a decimal number, the + and - operators add or subtract a fraction of the day (hours, minutes etc):

Oracle:

  -- Add 3 hours - n/24 expression to add n hours
  SELECT SYSDATE + 3/24 FROM dual;
  /* 2025-05-29 16:33:31 */
 
  -- Add 3 minutes - n/1440 or n/(24*60) expression to add n minutes
  SELECT SYSDATE + 3/1440 FROM dual;
  /* 2025-05-29 13:36:31 */
 
  -- Add 3 seconds - n/86400 or n/(24*60*60) expression to add n seconds
  SELECT SYSTIMESTAMP + 3/86400 FROM dual;
  /* 2025-05-29 13:33:34 */
 
  -- Add 1 day and 3 hours
  SELECT SYSDATE + 1.125 FROM dual;
   /* 2025-05-30 16:33:31 */

MySQL supports interval expressions to add or subtract a day fraction:

MySQL:

  -- Add 3 hours
  SELECT NOW() + INTERVAL '3' HOUR;
  /* 2025-05-29 16:33:31 */
 
  -- Add 3 minutes
  SELECT NOW() + INTERVAL '3' MINUTE;
  /* 2025-05-29 13:36:31 */
 
  -- Add 3 seconds
  SELECT NOW() + INTERVAL '3' SECOND;
  /* 2025-05-29 13:33:34 */
 
  -- Add 1 day and 3 hours
  SELECT NOW() + INTERVAL (1.125) * 86400 SECOND;
  /* 2025-05-30 16:33:31 */

In Oracle, if you subtract two DATE values that have different time parts then the result of the subtraction is a decimal value where the fractional part represents the fraction of day (for example, 0.5 means 12 hours):

Oracle:

  -- Get the number of days between 2 dates with different time
  SELECT 
     TO_DATE('29-MAY-2025 18:45:26', 'DD-MON-YYYY HH24:MI:SS') -  
     TO_DATE('28-MAY-2025 10:18:04', 'DD-MON-YYYY HH24:MI:SS') 
   FROM dual;
  /* 1.35233796 */

In MySQL, the difference between two datetime values produces a numeric result by implicitly converting two datetimes to numbers:

MySQL:

  -- Result of 20250529184526 - 20250528101804 numeric (!) subtraction
  SELECT 
    STR_TO_DATE('29-MAY-2025 18:45:26', '%d-%b-%Y %H:%i:%s') -  
    STR_TO_DATE('28-MAY-2025 10:18:04', '%d-%b-%Y %H:%i:%s');
    /* 1082722 */

So you can use the following expression to get the same result as in Oracle:

MySQL:

  -- Get the number of days between 2 dates with different time
  SELECT 
    TIMESTAMPDIFF(SECOND,
      STR_TO_DATE('29-MAY-2025 18:45:26', '%d-%b-%Y %H:%i:%s'),  
      STR_TO_DATE('28-MAY-2025 10:18:04', '%d-%b-%Y %H:%i:%s')) / -86400; 
  /*  1.3523 */

In Oracle, if you subtract two TIMESTAMP values the result of the subtraction is an interval value:

Oracle:

  -- Get the difference between two timestamps as interval
  SELECT 
     TO_TIMESTAMP('29-MAY-2025 18:45:26', 'DD-MON-YYYY HH24:MI:SS') -  
     TO_TIMESTAMP('28-MAY-2025 10:18:04', 'DD-MON-YYYY HH24:MI:SS') 
   FROM dual;
  /* +000000001 08:27:22.000000000 */

MySQL currently does not support the INTERVAL data type.

For more information, see Oracle to MySQL Migration.