DATE ADD function in PostgreSQL

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?

2 Answers

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

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

You can use date + 1 to do the equivalent of dateadd(), but I do not think that your query does what you want to do.

You should use window functions, instead:

with plays as ( select distinct date, user_id from clickstream_videos where event_name = 'video_play' and user_id is not null
), nextdaywatch as ( select date, user_id, case when lead(date) over (partition by user_id order by date) = date + 1 then 1 else 0 end as user_view_next_day from plays
)
select date, count(*) as users_view_videos, sum(user_view_next_day) as users_view_next_day from nextdaywatch group by date order by date;