PostgreSQL - DATEADD Function - SQL Server to PostgreSQL Migration

Although PostgreSQL does not provide a DATEADD function similar to SQL Server, you can use datetime arithmetic with interval literals to get the same results.

SQL Server:

  -- Add 1 day to the current date November 21, 2012
  SELECT DATEADD(day, 1, GETDATE());
  /* 2012-11-22 17:22:01.423 */
 
  -- Add 1 quarter
  SELECT DATEADD(qq, 1, '2025-02-26');
  /* 2025-05-26 00:00:00.000 */

PostgreSQL:

  --  Add 1 day to the current date November 21, 2012
  SELECT CURRENT_DATE + INTERVAL '1 day';
  /* 2012-11-22 17:22:01 */
 
    -- Add 1 quarter
  SELECT INTERVAL '3 MONTHS' + '2025-02-26'::DATE;
  /* 2025-05-26 00:00:00 */

You can map unit values between SQL Server DATEADD and PostgreSQL INTERVAL literals as follows:

In the previous example, we added a constant to the datetime value. What expression to use when the interval is specified in a variable or column?

In PostgreSQL, you can use the following expression:

For example, assume there is a table:

  CREATE TABLE licenses
  (
      purchased DATE,
      valid INT
   );
 
   -- Insert an item purchased today, valid 31 days 
   INSERT INTO licenses VALUES (CURRENT_TIMESTAMP, 31);

Now to get the expiration date you can use the following queries:

SQL Server:

   -- Get expiration date
   SELECT DATEADD(day, valid, purchased) FROM licenses;
   # 2012-12-22

PostgreSQL:

   -- Get expiration date
   SELECT purchased + valid * INTERVAL '1 day' FROM licenses;
   # 2012-12-22 00:00:00

For more information, see SQL Server to PostgreSQL Migration.