Datetime Arithmetic - Oracle to PostgreSQL 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 PostgreSQL, you can use the + and - operators only for DATE (year, month and day only) values and only with integer numbers. You have to use INTERVAL expressions in other cases.

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('2024-12-13 13:33:31') + 1 FROM dual;
  /* 2024-12-14 13:33:31 */
 
  -- Subtract 1 day
  SELECT SYSDATE - 1 FROM dual;
  /* 2024-12-12 13:33:31 */
 
  -- Using TIMESTAMP value 
  SELECT SYSTIMESTAMP - 1 FROM dual;
  /* 2024-12-12 13:33:31 */

PostgreSQL:

  -- Add 1 day (DATE does not include time) 
  SELECT DATE '2024-12-13' + 1;
  /* 2024-12-14 */
 
   -- Subtract 1 day
  SELECT CURRENT_DATE - 1;
  /* 2024-12-12 */
 
  -- Trying with TIMESTAMP
  SELECT NOW() - 1;
  /* ERROR:  operator does not exist: timestamp with time zone - integer */
 
  -- Using INTERVAL expression
  SELECT NOW() - INTERVAL '1 DAY';
  /* 2024-12-12 13:33:31.503335+01 */

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;
  /* 2024-12-13 16:33:31 */
 
  -- Add 3 minutes - n/1440 or n/(24*60) expression to add n minutes
  SELECT SYSDATE + 3/1440 FROM dual;
  /* 2024-12-13 13:36:31 */
 
  -- Add 3 seconds - n/86400 or n/(24*60*60) expression to add n seconds
  SELECT SYSTIMESTAMP + 3/86400 FROM dual;
  /* 2024-12-13 13:33:34 */
 
  -- Add 1 day and 3 hours
  SELECT SYSDATE + 1.125 FROM dual;
   /* 2024-12-14 16:33:31 */

PostgreSQL supports multiple expressions to add or subtract a day fraction:

PostgreSQL:

  -- Add 3 hours
  SELECT NOW() + 3/24::FLOAT * INTERVAL '1 DAY';
  SELECT NOW() + INTERVAL '3 HOUR';
  /* 2024-12-13 16:33:31 */

Adding minutes:

  -- Add 3 minutes
  SELECT NOW() + 3/1440::FLOAT * INTERVAL '1 DAY';
  SELECT NOW() + INTERVAL '3 MINUTE';
  /* 2024-12-13 13:36:31 */

Adding seconds:

  -- Add 3 seconds
  SELECT NOW() + 3/86400::FLOAT * INTERVAL '1 DAY';
  SELECT NOW() + INTERVAL '3 SECOND';
  /* 2024-12-13 13:33:34 */

Adding days and hours:

  -- Add 1 day and 3 hours
  SELECT NOW() + 1.125 * INTERVAL '1 DAY';
  SELECT NOW() + INTERVAL '1.125 DAY';
  SELECT NOW() + INTERVAL '1 DAY 3 HOUR';
  /* 2024-12-14 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('14-DEC-2024 18:45:26', 'DD-MON-YYYY HH24:MI:SS') -  
     TO_DATE('13-DEC-2024 10:18:04', 'DD-MON-YYYY HH24:MI:SS') 
   FROM dual;
  /* 1.35233796 */

In PostgreSQL, a DATE value does not include time, and the difference between two timestamps produces an interval, so you can use the following expression to get a decimal value similar to Oracle:

PostgreSQL:

  -- Get the number of days between 2 dates with different time
  SELECT EXTRACT(EPOCH FROM
     TO_TIMESTAMP('14-DEC-2024 18:45:26', 'DD-MON-YYYY HH24:MI:SS') -  
     TO_TIMESTAMP('13-DEC-2024 10:18:04', 'DD-MON-YYYY HH24:MI:SS')) / 86400; 
  /*  1.3523379629629630 */

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('14-DEC-2024 18:45:26', 'DD-MON-YYYY HH24:MI:SS') -  
     TO_TIMESTAMP('13-DEC-2024 10:18:04', 'DD-MON-YYYY HH24:MI:SS') 
   FROM dual;
  /* +000000001 08:27:22.000000000 */

In PostgreSQL, you also get an interval value if you subtract two TIMESTAMP values:

PostgreSQL:

  -- Get the difference between two timestamps as interval
  SELECT 
     TO_TIMESTAMP('14-DEC-2024 18:45:26', 'DD-MON-YYYY HH24:MI:SS') -  
     TO_TIMESTAMP('13-DEC-2024 10:18:04', 'DD-MON-YYYY HH24:MI:SS'); 
  /* 1 day 08:27:22 */

For more information, see Oracle to PostgreSQL Migration.