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:
datetime + variable * INTERVAL '1 day' |
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.