DATE ADD function in PostgreSQL

Question

I currently have the following code in Microsoft SQL Server to get users that viewed on two days in a row.

WITH uservideoviewvideo (date, user_id) AS (
  SELECT  DISTINCT date, user_id 
  FROM clickstream_videos
  WHERE event_name ='video_play'  
    and user_id IS NOT NULL
) 
SELECT currentday.date AS date, 
       COUNT(currentday.user_id) AS users_view_videos, 
       COUNT(nextday.user_id) AS users_view_next_day 
FROM userviewvideo currentday
  LEFT JOIN userviewvideo nextday 
         ON currentday.user_id = nextday.user_id AND DATEADD(DAY, 1, 
currentday.date) = nextday.date
GROUP BY currentday.date

I am trying to get the DATEADD function to work in PostgreSQL but I've been unable to figure out how to get this to work. Any suggestions?

Answer 1

I don't think PostgreSQL really has a DATEADD function. Instead, just do:

+ INTERVAL '1 day'

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
SELECT CURRENT_DATE + 1; # 2012-11-22 17:22:01

http://www.sqlines.com/postgresql/how-to/dateadd

EDIT:

It might be useful if you're using a dynamic length of time to create a string and then cast it as an interval like:

+ (col_days || ' days')::interval