SYSDATE + 1/24 - Datetime Arithmetic - Oracle to Snowflake Migration

Datetime arithmetic involves addition (+) and subtraction (-) operators on date and time values.

In Oracle, if you use the (+) operator to add an integer value to a datetime, you add days:

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- Add 3 days to the current day
  SELECT SYSDATE + 3 FROM dual;
  # 2022-04-27 21:24:13

In Snowflake you have to use the DATEADD function as follows:

Snowflake:

  -- Add 3 days to the current day
  SELECT DATEADD(DAY, 3, CURRENT_TIMESTAMP(0));
  # 2022-04-27 21:24:13.227 +0000

To subtract days, just use - operator instead of + in Oracle, or DATEADD with a negative integer value in Snowflake.

In Oracle, you can use n/24 expression to add n hours to a datetime:

Oracle:

  -- Add 3 hours to the current datetime
  SELECT SYSDATE + 3/24 FROM dual;
  # 2022-04-25 00:33:14
 
  -- Subtract 3 hours from the current datetime
  SELECT SYSDATE - 3/24 FROM dual;
  # 2022-04-25 18:33:14

In Snowflake you have to use the DATEADD function as follows:

Snowflake:

  -- Add 3 hours to the current datetime
  SELECT DATEADD(HOUR, 3, CURRENT_TIMESTAMP(0));
  # 2022-04-25 00:33:14.057 +0000
 
  -- Subtract 3 hours from the current datetime
  SELECT DATEADD(HOUR, -3, CURRENT_TIMESTAMP(0));
  # 2022-04-25 18:33:14.057 +0000

In Oracle, you can use n/1440 or n/(24*60) expression to add n minutes to a datetime:

Oracle:

  -- Add 3 minutes to the current datetime
  SELECT SYSDATE + 3/1440 FROM dual;
  # 2022-04-25 21:36:14
 
  -- Subtract 3 minutes from the current datetime
  SELECT SYSDATE - 3/1440 FROM dual;
  # 2022-04-25 21:30:14

In Snowflake you have to use the DATEADD function as follows:

Snowflake:

  -- Add 3 minutes to the current datetime
  SELECT DATEADD(MINUTE, 3, CURRENT_TIMESTAMP(0));
  # 2022-04-25 21:36:14.057 +0000
 
  -- Subtract 3 minutes from the current datetime
  SELECT DATEADD(MINUTE, -3, CURRENT_TIMESTAMP(0));
  # 2022-04-25 21:30:14.057 +0000

In Oracle, you can use n/86400 or n/(24*60*60) expression to add n seconds to a datetime:

Oracle:

  -- Add 3 seconds to the current datetime
  SELECT SYSDATE + 3/86400 FROM dual;
  # 2022-04-25 21:33:17
 
  -- Subtract 3 seconds from the current datetime
  SELECT SYSDATE - 3/86400 FROM dual;
  # 2022-04-25 21:33:11

In Snowflake you have to use the DATEADD function as follows:

Snowflake:

  -- Add 3 seconds to the current datetime
  SELECT DATEADD(SECOND, 3, CURRENT_TIMESTAMP(0));
  # 2022-04-25 21:33:17.057 +0000
 
  -- Subtract 3 seconds from the current datetime
  SELECT DATEADD(SECOND, -3, CURRENT_TIMESTAMP(0));
  # 2022-04-25 21:33:11.057 +0000

For more information, see Oracle to Snowflake Migration.