PostgreSQL - DATEADD - Add Interval to Datetime

Although PostgreSQL does not provide DATEADD function similar to SQL Server, Sybase or MySQL, 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

PostgreSQL:

  --  Add 1 day to the current date November 21, 2012
  SELECT CURRENT_DATE + INTERVAL '1 day';
  # 2012-11-22 17:22:01

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.