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

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

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - November 2012.